prompt 459 医技诊疗项目统计报表过程 SP_YJ_ZLXMTJ
create or replace procedure SP_YJ_ZLXMTJ
(
    as_KSRQ00    in    char,             --开始日期
    as_JSRQ00    in    char,             --结束日期
    as_KSBH00    in    number,           --执行科室编号(-1为全部执行科室)
    as_TJLB00    in    char,             --统计类别, '1' 按收费, '2' 按诊疗
    as_TJFS00    in    char,             --统计方式, '1' 汇总, '2' 明细
    as_TJLX00    in    char default '0', --统计类型, '0' 全部, '1' 门诊, '2' 住院
    ad_ID0000    out   number,           --统计报表对应ID
    ad_YHMSG0    out   varchar,          --存储过程提示的错误信息
    ad_SYSMSG    out   varchar,          --系统提示的错误信息
    as_KSFZTJ    in    char default '0', --收费统计与诊疗统计，是否按执行科室分组统计，0否1是 
    as_RQFS00    in    char default '0'  --0按记帐日期，1按执行日期
)
-- MODIFICATION HISTORY
-- Person      Date        Comments
-- linzetao    2020.01.13  created  for     YJ9-20190929-002   新建医技诊疗项目统计报表过程
-- linzetao    2020.02.21  修正医技单退费后ZLXMID变成0后无法统计问题   for YJ9-20200217-001
-- luowf       2020.02.28  科室明细统计【按收费】新增查询开单科室名称，存放在临时表BM_ZLXMTJ.ZLXMJC  YJ9-20200226-001
-- luowf       2020.04.29  as_KSBH00为-1时查全部的      YJ9-20200415-001
-- jlg         2020.05.11  收费明细统计增加门诊住院类型 YJ9-20200509-001
-- linzetao    2020.12.18  修正当SF_BRFY00.MZH000不全为数字时按诊疗统计报错问题      YJ9-20201215-001
-- luowf       2021.01.07  总计行的数量不显示为null，显示出实际的数据     YJ9-20201229-002
-- chenHeyi    2022.07.25  增加入参as_KSFZTJ， CUR_ZY_FYMX00_SF_TJ，CUR_ZY_FYMX00_ZL_TJ 增加执行科室编号字段    YJ9-20220713-001
-- linzetao    2023.06.16  统计方式为2(按收费明细统计、按诊疗明细统计)时增加门诊挂号科室或住院当前科室   SSMZ9-20230612-001
-- linminghan  2023.07.13  按收费明细统计增加年龄,执行日期,执行时间; for YJ9-20230628-002
-- chenHeyi    2023.08.30  增加入参as_RQFS00 ，as_RQFS00=1时另建CUR_ZY_FYMX00_SF_ZXRQ_TJ，CUR_ZY_FYMX00_SF_ZXRQ_MX(因ZY_FYMX00.ZXRQ00不准确)      YJ9-20230824-001
-- chenHeyi    2023.12.11  调整CUR_ZY_FYMX00_SF_ZXRQ_TJ,CUR_ZY_FYMX00_SF_ZXRQ_MX查询，去yj_ywjj00表关联     YJ9-20231211-001
-- chenHeyi    2024.04.15  诊疗统计，取单价改为用YJ_YW0000的金额/数量         YJ9-20240410-002
-- chenHeyi    2024.09.03  去末尾commit （长乐妇幼commit后，临时表BM_ZLXMTJ马上没数据)   YJ9-20240903-001   
-- chenHeyi    2024.10.24  重构SP_YJ_ZLXMTJ过程，优化查询效率（泉州德诚）                YJ9-20241021-001
-- chenHeyi    2024.12.02  按诊疗的记账日期查询(用SF_FYMX00,ZY_FYMX00）太慢，改为用SF_BRFY00,ZY_BRFY00       YJ9-20241202-001
-- chenHeyi    2025.05.06  入参as_KSBH00有-1或0的情况    
-------------------------------------------------------------------------------------------------------------------------- 
as
    ls_ZYHGHH    BM_ZLXMTJ.ZYHGHH%type;
    ls_BRXM00    BM_ZLXMTJ.BRXM00%type;
    ls_XB0000    BM_ZLXMTJ.XB0000%type;
    ls_HSXMID    BM_ZLXMTJ.HSXMID%type;
    ls_HSXMMC    BM_ZLXMTJ.HSXMMC%type;
    ls_HSLBID    BM_ZLXMTJ.HSLBID%type;
    ls_HSLBMC    BM_ZLXMTJ.HSLBMC%type;
    ls_ZLXMJC    BM_ZLXMTJ.ZLXMJC%type;
    ls_XMBH00    BM_ZLXMTJ.XMBH00%type;
    ls_XMMC00    BM_ZLXMTJ.XMMC00%type;
    ls_DJ0000    BM_ZLXMTJ.DJ0000%type;
    ls_SL0000    BM_ZLXMTJ.SL0000%type;
    ls_JE0000    BM_ZLXMTJ.JE0000%type;
    ls_XSXH00    BM_ZLXMTJ.XSXH00%type;
    ls_KDKSMC    BM_BMBM00.BMMC00%type;
    ls_MZZYLX    BM_ZLXMTJ.MZZYLX%type;
    ls_KDKSBH    BM_ZLXMTJ.KDKSBH%type;
    ls_ZXKSBH    BM_ZLXMTJ.ZXKSBH%type;  --执行科室编号
    ls_DQKS00    BM_BMBM00.BMBH00%type;  --挂号科室或住院科室
    ls_NL0000    BM_ZLXMTJ.NL0000%type;  --挂号/入院时年龄
    ls_ZXRQ00    BM_ZLXMTJ.ZXRQ00%type;  --项目执行日期
    ls_ZXSJ00    BM_ZLXMTJ.ZXSJ00%type;  --项目执行时间
    ls_YYSFTJ_ID BM_YYSFTJ.ID0000%type;   
    
    
    --按收费统计      
    cursor CUR_ZY_FYMX00_SF_TJ is
       select HSXMID, HSXMMC, XMBH00, XMMC00, DJ0000, SL0000, JE0000, XSXH00, ZXKS00 from (
           select HSXMID, HSXMMC, XMBH00, XMMC00, DJ0000, SL0000, JE0000, XSXH00, ZXKS00 from (  
               select C.HSXMID, D.HSXMMC, C.XMBH00, C.XMMC00, C.DJ0000, C.SL0000, C.JE0000, 0 XSXH00, ZXKS00 from (     
                   select HSXMID, XMBH00, XMMC00, DJ0000, sum(SL0000) SL0000, sum(ZJE000) JE0000,ZXKS00 from (      
                       select HSXMID, XMBH00, XMMC00, XMDJ00 DJ0000, nvl(XMSL00, 0) SL0000, nvl(HJJE00, 0) ZJE000,decode(as_KSFZTJ,'1',b.ZXKS00,as_KSBH00) as ZXKS00 from SF_FYMX00 B 
                         where B.SFLB00<>'3' 
                           and (as_TJLX00 ='0' or as_TJLX00 = '1')
                           and (B.CZRQ00 >= as_KSRQ00  and B.CZRQ00 <= as_JSRQ00)      
                           and (b.ZXKS00+0 = as_KSBH00 or as_KSBH00=0 or as_KSBH00=-1)
                       union all       
                       select HSXMID, XMBH00, XMMC00, XMDJ00 DJ0000, nvl(XMSL00, 0) SL0000, nvl(HJJE00, 0) ZJE000,decode(as_KSFZTJ,'1',b.ZXKS00,as_KSBH00) as ZXKS00 from ZY_FYMX00 B 
                         where B.SFLB00<>'3' 
                           and (as_TJLX00 = '0' or as_TJLX00 = '2')
                           and (B.CZRQ00 >= as_KSRQ00  and B.CZRQ00 <= as_JSRQ00)         
                           and (b.ZXKS00+0 = as_KSBH00 or as_KSBH00=0 or as_KSBH00=-1)
                   ) Y     
                   group by HSXMID, XMBH00, XMMC00, DJ0000,ZXKS00
               ) C, BM_YYHSXM D   
                 where C.HSXMID = D.HSXMID
           )  
           union all 
           select HSXMID, decode(HSXMID,null,'总计：','         小计：') HSXMMC, null XMBH00, null XMMC00, null DJ0000, decode(HSXMID,null,ZSL000,null) SL0000, JE0000, XSXH00,null as ZXKS00 from (  
               select C.HSXMID, D.HSXMMC, null XMBH00, null XMMC00, null DJ0000, C.ZSL000, C.JE0000, 99999 XSXH00 from (     
                   select HSXMID, sum(ZJE000) JE0000,sum(SL0000) ZSL000 from (      
                       select HSXMID, XMBH00, XMMC00, XMDJ00 DJ0000, nvl(XMSL00, 0) SL0000, nvl(HJJE00, 0) ZJE000 from SF_FYMX00 B 
                         where B.SFLB00<>'3' 
                           and (as_TJLX00 = '0' or as_TJLX00 = '1')
                           and (B.CZRQ00 >= as_KSRQ00  and B.CZRQ00 <= as_JSRQ00)          
                           and (b.ZXKS00+0 = as_KSBH00 or as_KSBH00=0 or as_KSBH00=-1)
                       union all       
                       select HSXMID, XMBH00, XMMC00, XMDJ00 DJ0000, nvl(XMSL00, 0) SL0000, nvl(HJJE00, 0) ZJE000 from ZY_FYMX00 B 
                         where B.SFLB00<>'3' 
                           and (as_TJLX00 = '0' or as_TJLX00 = '2')
                           and (B.CZRQ00 >= as_KSRQ00  and B.CZRQ00 <= as_JSRQ00)         
                           and (b.ZXKS00+0 = as_KSBH00 or as_KSBH00=0 or as_KSBH00=-1)
                   ) Y     
                   group by rollup(HSXMID)
               ) C, BM_YYHSXM D   
                 where C.HSXMID = D.HSXMID(+) 
           )  
       )
         order by HSXMID, XSXH00;
         
    --按收费统计(按执行日期）      
    cursor CUR_ZY_FYMX00_SF_ZXRQ_TJ is
       select HSXMID, HSXMMC, XMBH00, XMMC00, DJ0000, SL0000, JE0000, XSXH00, ZXKS00 from (
           select HSXMID, HSXMMC, XMBH00, XMMC00, DJ0000, SL0000, JE0000, XSXH00, ZXKS00 from (  
               select C.HSXMID, D.HSXMMC, C.XMBH00, C.XMMC00, C.DJ0000, C.SL0000, C.JE0000, 0 XSXH00, ZXKS00 from (     
                   select HSXMID, XMBH00, XMMC00, DJ0000, sum(SL0000) SL0000, sum(ZJE000) JE0000,ZXKS00 from (      
                       select HSXMID, XMBH00, XMMC00, XMDJ00 DJ0000, nvl(XMSL00, 0) SL0000, nvl(HJJE00, 0) ZJE000,decode(as_KSFZTJ,'1',b.ZXKS00,as_KSBH00) as ZXKS00 from SF_FYMX00 B,YJ_YW0000 h 
                         where B.SFLB00<>'3' 
                           and (as_TJLX00 ='0' or as_TJLX00 = '1')
                           and (h.ZXRQ00 >= as_KSRQ00  and h.ZXRQ00 <= as_JSRQ00) 
                           and B.YJDJH0=h.YJDJH0 and B.BRID00=h.BRID00 and h.MZZYBZ='0'       
                           and (b.ZXKS00+0 = as_KSBH00 or as_KSBH00=0 or as_KSBH00=-1)
                       union all       
                       select B.HSXMID, B.XMBH00, B.XMMC00, B.XMDJ00 DJ0000, nvl(B.XMSL00, 0) SL0000, nvl(B.HJJE00, 0) ZJE000,decode(as_KSFZTJ,'1',b.ZXKS00,as_KSBH00) as ZXKS00 from ZY_FYMX00 B,YJ_YW0000 h
                         where B.SFLB00<>'3' 
                           and (as_TJLX00 = '0' or as_TJLX00 = '2')                       
                           and B.DJID00=h.YJDJH0 and h.ZXRQ00 >= as_KSRQ00  and h.ZXRQ00 <= as_JSRQ00 
                           and b.ZYID00=h.ZYGHID and h.MZZYBZ='1'          
                           and (b.ZXKS00+0 = as_KSBH00 or as_KSBH00=0 or as_KSBH00=-1)                   
                   ) Y     
                   group by HSXMID, XMBH00, XMMC00, DJ0000,ZXKS00
               ) C, BM_YYHSXM D   
                 where C.HSXMID = D.HSXMID
           )  
           union all 
           select HSXMID, decode(HSXMID,null,'总计：','         小计：') HSXMMC, null XMBH00, null XMMC00, null DJ0000, decode(HSXMID,null,ZSL000,null) SL0000, JE0000, XSXH00,null as ZXKS00 from (  
               select C.HSXMID, D.HSXMMC, null XMBH00, null XMMC00, null DJ0000, C.ZSL000, C.JE0000, 99999 XSXH00 from (     
                   select HSXMID, sum(ZJE000) JE0000,sum(SL0000) ZSL000 from (      
                       select B.HSXMID, B.XMBH00, B.XMMC00, B.XMDJ00 DJ0000, nvl(B.XMSL00, 0) SL0000, nvl(B.HJJE00, 0) ZJE000 from SF_FYMX00 B,YJ_YW0000 h 
                         where B.SFLB00<>'3' 
                           and (as_TJLX00 = '0' or as_TJLX00 = '1')
                           and ( h.ZXRQ00 >= as_KSRQ00  and h.ZXRQ00 <= as_JSRQ00) 
                           and B.YJDJH0=h.YJDJH0 and B.BRID00=h.BRID00 and h.MZZYBZ='0'        
                           and (b.ZXKS00+0 = as_KSBH00 or as_KSBH00=0 or as_KSBH00=-1)
                       union all       
                       select B.HSXMID, B.XMBH00, B.XMMC00, B.XMDJ00 DJ0000, nvl(B.XMSL00, 0) SL0000, nvl(B.HJJE00, 0) ZJE000 from ZY_FYMX00 B,YJ_YW0000 h  
                         where B.SFLB00<>'3' 
                           and (as_TJLX00 = '0' or as_TJLX00 = '2')                      
                           and B.DJID00=h.YJDJH0 and h.ZXRQ00 >= as_KSRQ00  and h.ZXRQ00 <= as_JSRQ00 
                           and b.ZYID00=h.ZYGHID and h.MZZYBZ='1'           
                           and (b.ZXKS00+0 = as_KSBH00 or as_KSBH00=0 or as_KSBH00=-1)
                   ) Y     
                   group by rollup(HSXMID)
               ) C, BM_YYHSXM D   
                 where C.HSXMID = D.HSXMID(+) 
           )  
       )
         order by HSXMID, XSXH00;
    
    --按收费明细统计     
    cursor CUR_ZY_FYMX00_SF_MX is
       select ZYHGHH,MZZYLX, BRXM00, XB0000, HSXMID, HSXMMC, XMBH00, XMMC00, DJ0000, SL0000, JE0000, XSXH00, KDKS00, DQKS00, NL0000, ZXRQ00, ZXSJ00 from (
           select ZYHGHH,MZZYLX, BRXM00, XB0000, HSXMID, HSXMMC, XMBH00, XMMC00, DJ0000, SL0000, JE0000, XSXH00, KDKS00, DQKS00, NL0000, ZXRQ00, ZXSJ00 from (  
               select ZYHGHH,MZZYLX, BRXM00, XB0000, C.HSXMID, D.HSXMMC, C.XMBH00, C.XMMC00, C.DJ0000, C.SL0000, C.JE0000, 0 XSXH00, KDKS00, DQKS00, NL0000, ZXRQ00, ZXSJ00 from (     
                   select ZYHGHH,MZZYLX, BRXM00, XB0000, HSXMID, XMBH00, XMMC00, DJ0000, sum(SL0000) SL0000, sum(ZJE000) JE0000, KDKS00, DQKS00, NL0000, ZXRQ00, ZXSJ00 from (      
                       select b.MZH000 ZYHGHH,'门诊' MZZYLX, a.BRXM00, A.BRXB00 XB0000, B.HSXMID, B.XMBH00, 
                              B.XMMC00, B.XMDJ00 DJ0000, nvl(B.XMSL00, 0) SL0000, nvl(B.HJJE00, 0) ZJE000, b.KDKS00,
                              C.GHKS00 as DQKS00,SF_XT_CSRQTONL_BZ(c.CSRQ00,'',c.GHRQ00,'','3') NL0000,b.ZXRQ00,b.ZXSJ00
                       from BM_BRXXB0 A, SF_FYMX00 B,SF_BRXXB0 c 
                         where A.BRID00=B.BRID00
                           and b.MZH000=c.GHH000
                           and B.SFLB00<>'3' 
                           and (as_TJLX00 = '0' or as_TJLX00 = '1')
                           and (B.CZRQ00 >= as_KSRQ00  and B.CZRQ00 <= as_JSRQ00)            
                           and (b.ZXKS00+0 = as_KSBH00 or as_KSBH00=0 or as_KSBH00=-1)
                       union all       
                       select B.ZYH000 ZYHGHH,'住院' MZZYLX, a.BRXM00, A.BRXB00 XB0000, B.HSXMID, B.XMBH00, 
                              B.XMMC00, B.XMDJ00 DJ0000, nvl(B.XMSL00, 0) SL0000, nvl(B.HJJE00, 0) ZJE000, b.KDKS00,
                              c.DQKS00 as DQKS00,SF_XT_CSRQTONL_BZ(c.CSRQ00,'',c.RYRQ00,'','3') NL0000,b.ZXRQ00,b.ZXSJ00
                       from BM_BRXXB0 A, ZY_FYMX00 B,ZY_BRXXB0 c
                         where A.BRID00=B.BRID00 
                           and b.ZYID00=c.ZYID00
                           and (as_TJLX00 = '0' or as_TJLX00 = '2')
                           and B.SFLB00<>'3' 
                           and (B.CZRQ00 >= as_KSRQ00  and B.CZRQ00 <= as_JSRQ00)          
                           and (b.ZXKS00+0 = as_KSBH00 or as_KSBH00=0 or as_KSBH00=-1)
                   ) Y     
                   group by ZYHGHH,MZZYLX, BRXM00, XB0000, HSXMID, XMBH00, XMMC00, DJ0000,KDKS00, DQKS00, NL0000, ZXRQ00, ZXSJ00
               ) C, BM_YYHSXM D   
                 where C.HSXMID = D.HSXMID
           )  
           union all 
           select ZYHGHH,MZZYLX, BRXM00,  XB0000, HSXMID, decode(ZYHGHH,null,'总计：', '         小计：') HSXMMC, 
                  null XMBH00, null XMMC00, null DJ0000, decode(ZYHGHH,null,ZSL000,null) SL0000, JE0000, XSXH00,null KDKS00, null DQKS00, null NL0000, null ZXRQ00, null ZXSJ00
           from (  
               select ZYHGHH,MZZYLX, BRXM00, XB0000, null HSXMID, null HSXMMC, null XMBH00, null XMMC00, null DJ0000, C.ZSL000, C.JE0000, 99999 XSXH00 
               from (     
                   select ZYHGHH,MZZYLX, BRXM00, null XB0000, sum(ZJE000) JE0000,sum(SL0000) ZSL000 from (      
                       select b.MZH000 ZYHGHH,'门诊' MZZYLX, a.BRXM00, A.BRXB00 XB0000, B.HSXMID, B.XMBH00, B.XMMC00, 
                              B.XMDJ00 DJ0000, nvl(B.XMSL00, 0) SL0000, nvl(B.HJJE00, 0) ZJE000        
                       from BM_BRXXB0 A, SF_FYMX00 B
                         where A.BRID00=B.BRID00 
                           and B.SFLB00<>'3' 
                           and (as_TJLX00 = '0' or as_TJLX00 = '1')
                           and (B.CZRQ00 >= as_KSRQ00  and B.CZRQ00 <= as_JSRQ00)       
                           and (b.ZXKS00+0 = as_KSBH00 or as_KSBH00=0 or as_KSBH00=-1)                           
                       union all       
                       select B.ZYH000 ZYHGHH,'住院' MZZYLX, a.BRXM00, A.BRXB00 XB0000, B.HSXMID, B.XMBH00, B.XMMC00, 
                              B.XMDJ00 DJ0000, nvl(B.XMSL00, 0) SL0000, nvl(B.HJJE00, 0) ZJE000        
                       from BM_BRXXB0 A, ZY_FYMX00 B
                         where A.BRID00=B.BRID00 
                           and B.SFLB00<>'3' 
                           and (as_TJLX00 = '0' or as_TJLX00 = '2')
                           and (B.CZRQ00 >= as_KSRQ00  and B.CZRQ00 <= as_JSRQ00)       
                           and (b.ZXKS00+0 = as_KSBH00 or as_KSBH00=0 or as_KSBH00=-1)
                       
                   ) Y     
                   group by rollup(ZYHGHH,MZZYLX, BRXM00)
                ) C   
           )  
           order by ZYHGHH,MZZYLX, XSXH00,HSXMID
       ) 
         where BRXM00 is not null or HSXMMC like '%总计%';
         
    --按收费明细统计(按执行日期)     
    cursor CUR_ZY_FYMX00_SF_ZXRQ_MX is
       select ZYHGHH,MZZYLX, BRXM00, XB0000, HSXMID, HSXMMC, XMBH00, XMMC00, DJ0000, SL0000, JE0000, XSXH00, KDKS00, DQKS00, NL0000, ZXRQ00, ZXSJ00 from (
           select ZYHGHH,MZZYLX, BRXM00, XB0000, HSXMID, HSXMMC, XMBH00, XMMC00, DJ0000, SL0000, JE0000, XSXH00, KDKS00, DQKS00, NL0000, ZXRQ00, ZXSJ00 from (  
               select ZYHGHH,MZZYLX, BRXM00, XB0000, C.HSXMID, D.HSXMMC, C.XMBH00, C.XMMC00, C.DJ0000, C.SL0000, C.JE0000, 0 XSXH00, KDKS00, DQKS00, NL0000, ZXRQ00, ZXSJ00 from (     
                   select ZYHGHH,MZZYLX, BRXM00, XB0000, HSXMID, XMBH00, XMMC00, DJ0000, sum(SL0000) SL0000, sum(ZJE000) JE0000, KDKS00, DQKS00, NL0000, ZXRQ00, ZXSJ00 from (      
                       select b.MZH000 ZYHGHH,'门诊' MZZYLX, a.BRXM00, A.BRXB00 XB0000, B.HSXMID, B.XMBH00, 
                              B.XMMC00, B.XMDJ00 DJ0000, nvl(B.XMSL00, 0) SL0000, nvl(B.HJJE00, 0) ZJE000, b.KDKS00,
                              C.GHKS00 as DQKS00,SF_XT_CSRQTONL_BZ(c.CSRQ00,'',c.GHRQ00,'','3') NL0000,b.ZXRQ00,b.ZXSJ00
                       from BM_BRXXB0 A, SF_FYMX00 B,SF_BRXXB0 c,YJ_YW0000 h 
                         where A.BRID00=B.BRID00
                           and b.MZH000=c.GHH000
                           and B.SFLB00<>'3' 
                           and (as_TJLX00 = '0' or as_TJLX00 = '1')
                           and ( h.ZXRQ00 >= as_KSRQ00  and h.ZXRQ00 <= as_JSRQ00)  
                           and B.YJDJH0=h.YJDJH0 and B.BRID00=h.BRID00 and h.MZZYBZ='0'            
                           and (b.ZXKS00+0 = as_KSBH00 or as_KSBH00=0 or as_KSBH00=-1)
                       union all       
                       select B.ZYH000 ZYHGHH,'住院' MZZYLX, a.BRXM00, A.BRXB00 XB0000, B.HSXMID, B.XMBH00, 
                              B.XMMC00, B.XMDJ00 DJ0000, nvl(B.XMSL00, 0) SL0000, nvl(B.HJJE00, 0) ZJE000, b.KDKS00,
                              c.DQKS00 as DQKS00,SF_XT_CSRQTONL_BZ(c.CSRQ00,'',c.RYRQ00,'','3') NL0000,b.ZXRQ00,b.ZXSJ00
                       from BM_BRXXB0 A, ZY_FYMX00 B,ZY_BRXXB0 c,YJ_YW0000 h
                         where A.BRID00=B.BRID00 
                           and b.ZYID00=c.ZYID00
                           and (as_TJLX00 = '0' or as_TJLX00 = '2')
                           and B.SFLB00<>'3' 
                           and h.ZXRQ00 >= as_KSRQ00  and h.ZXRQ00 <= as_JSRQ00  
                           and B.DJID00=h.YJDJH0 and B.ZYID00=h.ZYGHID and h.MZZYBZ='1'         
                           and (b.ZXKS00+0 = as_KSBH00 or as_KSBH00=0 or as_KSBH00=-1)
                   ) Y     
                   group by ZYHGHH,MZZYLX, BRXM00, XB0000, HSXMID, XMBH00, XMMC00, DJ0000,KDKS00, DQKS00, NL0000, ZXRQ00, ZXSJ00
               ) C, BM_YYHSXM D   
                 where C.HSXMID = D.HSXMID
           )  
           union all 
           select ZYHGHH,MZZYLX, BRXM00,  XB0000, HSXMID, decode(ZYHGHH,null,'总计：', '         小计：') HSXMMC, 
                  null XMBH00, null XMMC00, null DJ0000, decode(ZYHGHH,null,ZSL000,null) SL0000, JE0000, XSXH00,null KDKS00, null DQKS00, null NL0000, null ZXRQ00, null ZXSJ00
           from (  
               select ZYHGHH,MZZYLX, BRXM00, XB0000, null HSXMID, null HSXMMC, null XMBH00, null XMMC00, null DJ0000, C.ZSL000, C.JE0000, 99999 XSXH00 
               from (     
                   select ZYHGHH,MZZYLX, BRXM00, null XB0000, sum(ZJE000) JE0000,sum(SL0000) ZSL000 from (      
                       select b.MZH000 ZYHGHH,'门诊' MZZYLX, a.BRXM00, A.BRXB00 XB0000, B.HSXMID, B.XMBH00, B.XMMC00, 
                              B.XMDJ00 DJ0000, nvl(B.XMSL00, 0) SL0000, nvl(B.HJJE00, 0) ZJE000        
                       from BM_BRXXB0 A, SF_FYMX00 B,YJ_YW0000 h 
                         where A.BRID00=B.BRID00 
                           and B.SFLB00<>'3' 
                           and (as_TJLX00 = '0' or as_TJLX00 = '1')
                           and ( h.ZXRQ00 >= as_KSRQ00  and h.ZXRQ00 <= as_JSRQ00)  
                           and B.YJDJH0=h.YJDJH0 and B.BRID00=h.BRID00 and h.MZZYBZ='0'          
                           and (b.ZXKS00+0 = as_KSBH00 or as_KSBH00=0 or as_KSBH00=-1)                           
                       union all       
                       select B.ZYH000 ZYHGHH,'住院' MZZYLX, a.BRXM00, A.BRXB00 XB0000, B.HSXMID, B.XMBH00, B.XMMC00, 
                              B.XMDJ00 DJ0000, nvl(B.XMSL00, 0) SL0000, nvl(B.HJJE00, 0) ZJE000        
                       from BM_BRXXB0 A, ZY_FYMX00 B,YJ_YW0000 h
                         where A.BRID00=B.BRID00 
                           and B.SFLB00<>'3' 
                           and (as_TJLX00 = '0' or as_TJLX00 = '2')
                           and h.ZXRQ00 >= as_KSRQ00  and h.ZXRQ00 <= as_JSRQ00 
                           and B.DJID00=h.YJDJH0 and B.ZYID00=h.ZYGHID and h.MZZYBZ='1'           
                           and (b.ZXKS00+0 = as_KSBH00 or as_KSBH00=0 or as_KSBH00=-1)
                       
                   ) Y     
                   group by rollup(ZYHGHH,MZZYLX, BRXM00)
                ) C   
           )  
           order by ZYHGHH,MZZYLX, XSXH00,HSXMID
       ) 
         where BRXM00 is not null or HSXMMC like '%总计%';
    
    --按诊疗统计             
    cursor CUR_ZY_FYMX00_ZL_TJ is
       select HSLBID, HSLBMC, ZLXMJC, DJ0000, SL0000, JE0000, ZXKS00 from (
           select 0 HSLBID, ' ' HSLBMC, nvl(D.ZLXMJC,C.ZLXMID) ZLXMJC, 
                  --(select sum(nvl(f.TCJE00,g.SFJE00)*f.SFCS00) from XT_ZLSFGX f,BM_YYSFXM g where c.ZLXMID=f.ZLXMID and g.SFXMID=f.SFXMID) as DJ0000, 
                  c.DJ0000,c.SL0000, c.JE0000, c.ZXKS00 from ( 
               select sum(y.SL0000) SL0000, sum(y.ZJE000) JE0000, Y.DJ0000,y.ZLXMID,y.ZXKS00 from (  
                   select decode(A.SL0000, 0, 1, A.SL0000) SL0000, nvl(A.ZJE000, 0) ZJE000, nvl(A.ZJE000, 0)/decode(A.SL0000, 0, 1, A.SL0000) as DJ0000, 
                          nvl((select max(ZLXMID) from YJ_YW0000 where YJDJH0=a.CXDJH0),a.ZLXMID) ZLXMID,decode(as_KSFZTJ,'1',a.YJKSBH,as_KSBH00) as ZXKS00
                   from YJ_YW0000 A,ZY_BRFY00 B
                     where 1=1 
                       and A.ZYGHID = B.ZYID00
                       and A.SFDJH0 = B.DJH000
                       and ((as_RQFS00='0' and B.CZRQ00 >= as_KSRQ00 and B.CZRQ00 <= as_JSRQ00) or (as_RQFS00='1' and A.ZXRQ00 >= as_KSRQ00 and A.ZXRQ00 <= as_JSRQ00 ))
                       and A.XMZT00 in ('2','3','4') 
                       and A.MZZYBZ = '1' 
                       and (as_TJLX00 = '0' or as_TJLX00 = '2')
                       and (A.YJKSBH+0 = as_KSBH00 or as_KSBH00=-1) 
                   union all  
                   select decode(A.SL0000, 0, 1, A.SL0000) SL0000, nvl(A.ZJE000,0) ZJE000, nvl(A.ZJE000, 0)/decode(A.SL0000, 0, 1, A.SL0000) as DJ0000,
                          nvl((select max(ZLXMID) from YJ_YW0000 where YJDJH0=a.CXDJH0),a.ZLXMID) ZLXMID,decode(as_KSFZTJ,'1',a.YJKSBH,as_KSBH00) as ZXKS00 
                   from YJ_YW0000 A,SF_BRFY00 B
                     where 1=1 
                       and A.ZYHGHH = B.MZH000
                       and A.SFDJH0 = B.DJH000
                       and ((as_RQFS00='0' and B.CZRQ00 >= as_KSRQ00 and B.CZRQ00 <= as_JSRQ00) or (as_RQFS00='1' and A.ZXRQ00 >= as_KSRQ00 and A.ZXRQ00 <= as_JSRQ00 ))    
                       and A.XMZT00 IN ('2','3','4') 
                       and A.MZZYBZ <> '1' 
                       and (as_TJLX00 = '0' or as_TJLX00 = '1')
                       and (A.YJKSBH+0 = as_KSBH00 or as_KSBH00=-1)
               ) Y group by ZLXMID,ZXKS00,DJ0000
           ) C, BM_ZLZD00 D 
             where C.ZLXMID = D.ZLXMID(+)
       )  
       union 
       select null HSLBID, null HSLBMC, '总计：' ZLXMJC, null DJ0000, sum(SL0000), sum(JE0000), null as ZXKS00 from (  
         select y.ZLXMID, y.JE0000,y.SL0000 from (    
             select nvl((select max(ZLXMID) from YJ_YW0000 where YJDJH0=A.CXDJH0),a.ZLXMID) ZLXMID, nvl(A.ZJE000, 0) JE0000,
                    decode(A.SL0000, 0, 1, A.SL0000) SL0000 
             from YJ_YW0000 A,ZY_BRFY00 B 
               where 1=1 
                 and A.ZYGHID = B.ZYID00
                 and A.SFDJH0 = B.DJH000
                 and ((as_RQFS00='0' and B.CZRQ00 >= as_KSRQ00 and B.CZRQ00 <= as_JSRQ00) or (as_RQFS00='1' and A.ZXRQ00 >= as_KSRQ00 and A.ZXRQ00 <= as_JSRQ00 ))
                 and A.XMZT00 in ('2','3','4') 
                 and A.MZZYBZ = '1' 
                 and (as_TJLX00 = '0' or as_TJLX00 = '2')
                 and (A.YJKSBH+0 = as_KSBH00 or as_KSBH00=-1) 
             union all  
             select nvl((select max(ZLXMID) from YJ_YW0000 where YJDJH0=A.CXDJH0),a.ZLXMID) ZLXMID, nvl(A.ZJE000, 0) JE0000,
                    decode(A.SL0000, 0, 1, A.SL0000) SL0000 
             from YJ_YW0000 A,SF_BRFY00 B 
               where 1=1 
                 and A.ZYHGHH = B.MZH000
                 and A.SFDJH0 = B.DJH000
                 and ((as_RQFS00='0' and B.CZRQ00 >= as_KSRQ00 and B.CZRQ00 <= as_JSRQ00) or (as_RQFS00='1' and A.ZXRQ00 >= as_KSRQ00 and A.ZXRQ00 <= as_JSRQ00 ))
                 and A.XMZT00 IN ('2','3','4') 
                 and A.MZZYBZ <> '1' 
                 and (as_TJLX00 = '0' or as_TJLX00 = '1')
                 and (A.YJKSBH+0 = as_KSBH00 or as_KSBH00=-1)
         ) Y   
       ) 
         order by HSLBID; 

    --按诊疗明细统计
    cursor CUR_ZY_FYMX00_ZL_MX is
      select ZYHGHH, BRXM00, XB0000, HSLBID, HSLBMC, ZLXMJC, DJ0000, SL0000, JE0000, DQKS00 from (
        select ZYHGHH, BRXM00, XB0000, HSLBID, HSLBMC, ZLXMJC, DJ0000, SL0000, JE0000, DQKS00 from (
           select 0 HSLBID, ' ' HSLBMC, nvl(D.ZLXMJC,C.ZLXMID) ZLXMJC, 
                  --(select sum(nvl(f.TCJE00,g.SFJE00)*f.SFCS00) from XT_ZLSFGX f,BM_YYSFXM g where c.ZLXMID=f.ZLXMID and g.SFXMID=f.SFXMID) as DJ0000, 
                  c.ZYHGHH, c.BRXM00, c.XB0000, c.SL0000, c.JE0000,c.DJ0000, c.DQKS00 from ( 
               select y.ZYHGHH, y.BRXM00, y.XB0000, y.SL0000, y.JE0000,Y.DJ0000, y.ZLXMID, y.DQKS00 from ( 
                 select ZYHGHH, BRXM00, XB0000, sum(SL0000) SL0000, sum(JE0000) JE0000, DJ0000, ZLXMID, DQKS00 from( 
                   select a.ZYHGHH, a.BRXM00, a.XB0000, decode(A.SL0000, 0, 1, A.SL0000) SL0000, nvl(A.ZJE000, 0) JE0000, nvl(A.ZJE000, 0)/decode(A.SL0000, 0, 1, A.SL0000) as DJ0000,
                          nvl((select max(ZLXMID) from YJ_YW0000 where YJDJH0=a.CXDJH0),a.ZLXMID) ZLXMID,a.MZZYBZ,
                          decode(a.MZZYBZ,'1',(select max(DQKS00) from ZY_BRXXB0 where ZYID00=a.ZYGHID),(select max(GHKS00) from SF_BRXXB0 where GHID00=a.ZYGHID)) as DQKS00
                   from YJ_YW0000 A, ZY_BRFY00 B 
                     where A.ZYGHID = B.ZYID00 
                       and A.SFDJH0 = B.DJH000 
                       and ((as_RQFS00='0' and B.CZRQ00 >= as_KSRQ00 and B.CZRQ00 <= as_JSRQ00) or (as_RQFS00='1' and A.ZXRQ00 >= as_KSRQ00 and A.ZXRQ00 <= as_JSRQ00 ))   
                       and A.XMZT00 in ('2','3','4') 
                       and A.MZZYBZ = '1' 
                       and (as_TJLX00 = '0' or as_TJLX00 = '2')
                       and (A.YJKSBH+0 = as_KSBH00 or as_KSBH00=-1)
                       ) 
                 group by ZYHGHH, BRXM00, XB0000, ZLXMID, DQKS00,DJ0000
                 union all  
                 select ZYHGHH, BRXM00, XB0000, sum(SL0000) SL0000, sum(JE0000) JE0000,DJ0000, ZLXMID, DQKS00 from(
                   select a.ZYHGHH, a.BRXM00, a.XB0000, decode(A.SL0000, 0, 1, A.SL0000) SL0000, nvl(A.ZJE000, 0) JE0000,nvl(A.ZJE000, 0)/decode(A.SL0000, 0, 1, A.SL0000) as DJ0000,
                          nvl((select max(ZLXMID) from YJ_YW0000 where YJDJH0=a.CXDJH0),a.ZLXMID) ZLXMID,
                          decode(a.MZZYBZ,'1',(select max(DQKS00) from ZY_BRXXB0 where ZYID00=a.ZYGHID),(select max(GHKS00) from SF_BRXXB0 where GHID00=a.ZYGHID)) as DQKS00
                   from YJ_YW0000 A, SF_BRFY00 B 
                     where A.ZYHGHH = B.MZH000 
                       and A.SFDJH0 = B.DJH000 
                       and ((as_RQFS00='0' and B.CZRQ00 >= as_KSRQ00 and B.CZRQ00 <= as_JSRQ00) or (as_RQFS00='1' and A.ZXRQ00 >= as_KSRQ00 and A.ZXRQ00 <= as_JSRQ00 ))   
                       and A.XMZT00 IN ('2','3','4') 
                       and A.MZZYBZ <> '1' 
                       and (as_TJLX00 = '0' or as_TJLX00 = '1')
                       and (A.YJKSBH+0 = as_KSBH00 or as_KSBH00=-1)
                       )
                 group by ZYHGHH, BRXM00, XB0000, ZLXMID, DQKS00,DJ0000
               ) Y
           ) C, BM_ZLZD00 D 
             where C.ZLXMID = D.ZLXMID(+)
        )  
        union all
        select ZYHGHH, BRXM00, null XB0000, null HSLBID, null HSLBMC, decode(ZYHGHH,null,'总计: ','         小计：') ZLXMJC, null DJ0000, decode(ZYHGHH,null,SL0000,null) SL0000, JE0000, null as DQKS00 from (  
          select y.ZYHGHH, y.BRXM00, null XB0000, null ZLXMID, null ZLXMJC, sum(y.JE0000) JE0000,sum(y.SL0000) SL0000 from (    
            select a.ZYHGHH, a.BRXM00, a.XB0000,decode(A.SL0000, 0, 1, A.SL0000) SL0000 ,
                   nvl((select max(ZLXMID) from YJ_YW0000 where YJDJH0=a.CXDJH0),a.ZLXMID) ZLXMID, nvl(A.ZJE000, 0) JE0000       
            from YJ_YW0000 A, ZY_BRFY00 B 
              where A.ZYGHID = B.ZYID00 
                       and A.SFDJH0 = B.DJH000 
                       and ((as_RQFS00='0' and B.CZRQ00 >= as_KSRQ00 and B.CZRQ00 <= as_JSRQ00) or (as_RQFS00='1' and A.ZXRQ00 >= as_KSRQ00 and A.ZXRQ00 <= as_JSRQ00 ))  
                       and A.XMZT00 in ('2','3','4') 
                       and A.MZZYBZ = '1' 
                       and (as_TJLX00 = '0' or as_TJLX00 = '2')
                       and (A.YJKSBH+0 = as_KSBH00 or as_KSBH00=-1)   
            union all  
            select a.ZYHGHH, a.BRXM00, a.XB0000,decode(A.SL0000, 0, 1, A.SL0000) SL0000 ,
                   nvl((select max(ZLXMID) from YJ_YW0000 where YJDJH0=a.CXDJH0),a.ZLXMID) ZLXMID, nvl(A.ZJE000,0) JE0000     
            from YJ_YW0000 A, SF_BRFY00 B 
              where A.ZYHGHH = B.MZH000 
                and A.SFDJH0 = B.DJH000 
                and ((as_RQFS00='0' and B.CZRQ00 >= as_KSRQ00 and B.CZRQ00 <= as_JSRQ00) or (as_RQFS00='1' and A.ZXRQ00 >= as_KSRQ00 and A.ZXRQ00 <= as_JSRQ00 ))
                and A.XMZT00 IN ('2','3','4') 
                and A.MZZYBZ <> '1' 
                and (as_TJLX00 = '0' or as_TJLX00 = '1')
                and (A.YJKSBH+0 = as_KSBH00 or as_KSBH00=-1)
          ) Y group by rollup(y.ZYHGHH,y.BRXM00)
        ) 
        order by ZYHGHH, HSLBID
      ) y
      where y.BRXM00 is not null 
        or y.ZLXMJC like '%总计%';
       
begin
  ls_MZZYLX:='全部';
  select SQ_BM_ZLXMTJ_ID0000.nextval into ad_ID0000 from dual;
    
  /*if as_TJLB00='1' then
    if as_TJFS00='1' then
      if as_RQFS00='1' then
        open CUR_ZY_FYMX00_SF_ZXRQ_TJ;    
      else
        open CUR_ZY_FYMX00_SF_TJ; 
      end if;
    else
      if as_RQFS00='1' then
        open CUR_ZY_FYMX00_SF_ZXRQ_MX;
      else
        open CUR_ZY_FYMX00_SF_MX;
      end if;
    end if;
  else
    if as_TJFS00='1' then
      open CUR_ZY_FYMX00_ZL_TJ;
    else
      open CUR_ZY_FYMX00_ZL_MX;
    end if;
  end if;
  
  loop
    if as_TJLB00='1' then
      if as_TJFS00='1' then
        if as_RQFS00='1' then 
          fetch CUR_ZY_FYMX00_SF_ZXRQ_TJ into ls_HSXMID,ls_HSXMMC,ls_XMBH00,ls_XMMC00,ls_DJ0000,ls_SL0000,ls_JE0000,ls_XSXH00,ls_ZXKSBH;
          exit when CUR_ZY_FYMX00_SF_ZXRQ_TJ%notfound;
        else
          fetch CUR_ZY_FYMX00_SF_TJ into ls_HSXMID,ls_HSXMMC,ls_XMBH00,ls_XMMC00,ls_DJ0000,ls_SL0000,ls_JE0000,ls_XSXH00,ls_ZXKSBH;
          exit when CUR_ZY_FYMX00_SF_TJ%notfound;
        end if;
      else
        if as_RQFS00='1' then 
          fetch CUR_ZY_FYMX00_SF_ZXRQ_MX into ls_ZYHGHH,ls_MZZYLX,ls_BRXM00,ls_XB0000,ls_HSXMID,ls_HSXMMC,ls_XMBH00,ls_XMMC00,ls_DJ0000,ls_SL0000,ls_JE0000,ls_XSXH00,ls_KDKSBH,ls_DQKS00,ls_NL0000,ls_ZXRQ00,ls_ZXSJ00;
          exit when CUR_ZY_FYMX00_SF_ZXRQ_MX%notfound;
        else
          fetch CUR_ZY_FYMX00_SF_MX into ls_ZYHGHH,ls_MZZYLX,ls_BRXM00,ls_XB0000,ls_HSXMID,ls_HSXMMC,ls_XMBH00,ls_XMMC00,ls_DJ0000,ls_SL0000,ls_JE0000,ls_XSXH00,ls_KDKSBH,ls_DQKS00,ls_NL0000,ls_ZXRQ00,ls_ZXSJ00;
          exit when CUR_ZY_FYMX00_SF_MX%notfound;
        end if;
      end if;
    else
      if as_TJFS00='1' then
        fetch CUR_ZY_FYMX00_ZL_TJ into ls_HSLBID,ls_HSLBMC,ls_ZLXMJC,ls_DJ0000,ls_SL0000,ls_JE0000,ls_ZXKSBH;
        exit when CUR_ZY_FYMX00_ZL_TJ%notfound;
      else
        fetch CUR_ZY_FYMX00_ZL_MX into ls_ZYHGHH,ls_BRXM00,ls_XB0000,ls_HSLBID,ls_HSLBMC,ls_ZLXMJC,ls_DJ0000,ls_SL0000,ls_JE0000,ls_DQKS00;
        exit when CUR_ZY_FYMX00_ZL_MX%notfound;
      end if ;
    end if;
    
    if as_TJLB00='1' then
      if as_TJFS00='1' then
        insert into BM_ZLXMTJ(ID0000,HSXMID,HSXMMC,XMBH00,XMMC00,DJ0000,SL0000,JE0000,XSXH00,ZXKSBH)
          values(ad_ID0000,ls_HSXMID,ls_HSXMMC,ls_XMBH00,ls_XMMC00,ls_DJ0000,ls_SL0000,ls_JE0000,ls_XSXH00,ls_ZXKSBH);
      else       
        insert into BM_ZLXMTJ(ID0000,ZYHGHH,BRXM00,XB0000,HSXMID,HSXMMC,XMBH00,XMMC00,DJ0000,SL0000,JE0000,XSXH00,MZZYLX,KDKSBH,DQKS00,NL0000,ZXRQ00,ZXSJ00)
          values(ad_ID0000,ls_ZYHGHH,ls_BRXM00,ls_XB0000,ls_HSXMID,ls_HSXMMC,ls_XMBH00,
                 ls_XMMC00,ls_DJ0000,ls_SL0000,ls_JE0000,ls_XSXH00,ls_MZZYLX,ls_KDKSBH,ls_DQKS00,ls_NL0000,ls_ZXRQ00,ls_ZXSJ00);
      end if;
    else
      if as_TJFS00='1' then
        insert into BM_ZLXMTJ(ID0000,HSLBID,HSLBMC,ZLXMJC,DJ0000,SL0000,JE0000,ZXKSBH)
          values(ad_ID0000,ls_HSLBID,ls_HSLBMC,ls_ZLXMJC,ls_DJ0000,ls_SL0000,ls_JE0000,ls_ZXKSBH);
      else
        insert into BM_ZLXMTJ(ID0000,ZYHGHH,BRXM00,XB0000,HSLBID,HSLBMC,ZLXMJC,DJ0000,SL0000,JE0000,DQKS00)
          values(ad_ID0000,ls_ZYHGHH,ls_BRXM00,ls_XB0000,ls_HSLBID,ls_HSLBMC,ls_ZLXMJC,
                 ls_DJ0000,ls_SL0000,ls_JE0000,ls_DQKS00);
      end if;
    end if;
  end loop;
  
  if as_TJLB00='1' then
    if as_TJFS00='1' then
      if as_RQFS00='1' then
        close CUR_ZY_FYMX00_SF_ZXRQ_TJ;
      else
        close CUR_ZY_FYMX00_SF_TJ;
      end if;
    else
      if as_RQFS00='1' then
        close CUR_ZY_FYMX00_SF_ZXRQ_MX;
      else
        close CUR_ZY_FYMX00_SF_MX;
      end if;
    end if;
  else
    if as_TJFS00='1' then
      close CUR_ZY_FYMX00_ZL_TJ;
    else
      close CUR_ZY_FYMX00_ZL_MX;
    end if;
  end if;*/
  --BMBH00开单科室，SFXM09执行科室，SFXM10挂号或当前科室
  select  SQ_BM_YYSFTJ_ID0000.nextval  into  ls_YYSFTJ_ID  from  dual; 
  
  if as_TJLB00='1' then
     --按收费
     if as_RQFS00='0' then
        --按记帐日期
        if as_TJLX00='0' or as_TJLX00='1'then
           --门诊
           insert into BM_YYSFTJ(ID0000,BMBH00,  SFXM01,  BZSM01,  BZSM02,  BZSM03,  SFXM04,  SFXM05,  SJYSXM,  SFXM06,   
                   SFXM07,  SFXM08,  SFXM09,  SFXM10,  BZSM04, BZSM05,                                          BZSM06,  BZSM07, BZSM08, BZSM09)
	            select ls_YYSFTJ_ID,    a.KDKS00,a.BRID00,a.MZH000,c.BRXM00,c.BRXB00,a.HSXMID,a.XMBH00,a.XMMC00,a.XMDJ00, 
	               a.XMSL00,a.HJJE00,a.ZXKS00, b.GHKS00,a.YJDJH0, SF_XT_CSRQTONL_BZ(b.CSRQ00,'',b.GHRQ00,'','3'),a.ZXRQ00,a.ZXSJ00,'门诊' ,'00001'   --00001为数据
	               from SF_FYMX00 a,SF_BRXXB0 b, BM_BRXXB0 c
	                 where  a.BRID00=c.BRID00 
	                 and a.BRID00=b.BRID00 and a.MZH000=b.GHH000
	                 and a.SFLB00<>'3' and a.CZRQ00>=as_KSRQ00 and a.CZRQ00<=as_JSRQ00
	                 and (nvl(as_KSBH00,0)=0 or nvl(as_KSBH00,0)=-1 or a.ZXKS00=as_KSBH00);         
        end if;
        if as_TJLX00='0' or as_TJLX00='2'then 
           --住院
           insert into BM_YYSFTJ(ID0000,BMBH00,  SFXM01,  BZSM01,  BZSM02,  BZSM03,  SFXM04,  SFXM05,  SJYSXM,  SFXM06,   
                 SFXM07,  SFXM08,  SFXM09,  SFXM10,  BZSM04, BZSM05,                                            BZSM06,  BZSM07, BZSM08, BZSM09)
	            select ls_YYSFTJ_ID,    a.KDKS00,a.BRID00,a.ZYH000,c.BRXM00,c.BRXB00,a.HSXMID,a.XMBH00,a.XMMC00,a.XMDJ00, 
	               a.XMSL00,a.HJJE00,a.ZXKS00,b.DQKS00,a.DJID00, SF_XT_CSRQTONL_BZ(b.CSRQ00,'',b.RYRQ00,'','3'),a.ZXRQ00,a.ZXSJ00, '住院','00001' 
	               from ZY_FYMX00 a, ZY_BRXXB0 b, BM_BRXXB0 c
	                 where  a.BRID00=c.BRID00 
	                 and a.ZYID00=b.ZYID00
	                 and a.SFLB00<>'3' and a.CZRQ00>=as_KSRQ00 and a.CZRQ00<=as_JSRQ00
	                 and (nvl(as_KSBH00,0)=0 or nvl(as_KSBH00,0)=-1 or a.ZXKS00=as_KSBH00); 
        
        end if;
     else
        --按执行日期
        if as_TJLX00='0' or as_TJLX00='1'then
           --门诊
           insert into BM_YYSFTJ(ID0000,BMBH00,  SFXM01,  BZSM01,  BZSM02,  BZSM03,  SFXM04,  SFXM05,  SJYSXM,  SFXM06,   
                   SFXM07,  SFXM08,  SFXM09,  SFXM10,  BZSM04, BZSM05,                                          BZSM06,  BZSM07, BZSM08, BZSM09)
	            select ls_YYSFTJ_ID,    d.KDKSBH,a.BRID00,a.MZH000,c.BRXM00,c.BRXB00,a.HSXMID,a.XMBH00,a.XMMC00,a.XMDJ00, 
	               a.XMSL00,a.HJJE00,d.YJKSBH,b.GHKS00,a.YJDJH0, SF_XT_CSRQTONL_BZ(b.CSRQ00,'',b.GHRQ00,'','3'),d.ZXRQ00,d.ZXSJ00,'门诊' ,'00001'   --00001为数据
	               from SF_FYMX00 a, SF_BRXXB0 b, BM_BRXXB0 c, YJ_YW0000 d
	                 where  a.BRID00=c.BRID00 
	                 and a.BRID00=b.BRID00 and a.MZH000=b.GHH000
	                 and a.SFLB00<>'3' and (nvl(as_KSBH00,0)=0 or nvl(as_KSBH00,0)=-1 or d.YJKSBH=as_KSBH00) 
	                 and a.YJDJH0=d.YJDJH0 and a.BRID00=d.BRID00 and d.MZZYBZ='0' and d.ZXRQ00 >= as_KSRQ00  and d.ZXRQ00 <= as_JSRQ00;  	                 
        end if;
        
        if as_TJLX00='0' or as_TJLX00='2'then 
           --住院
           insert into BM_YYSFTJ(ID0000,BMBH00,  SFXM01,  BZSM01,  BZSM02,  BZSM03,  SFXM04,  SFXM05,  SJYSXM,  SFXM06,   
                 SFXM07,  SFXM08,  SFXM09,  SFXM10,  BZSM04, BZSM05,                                            BZSM06,  BZSM07, BZSM08, BZSM09)
	            select ls_YYSFTJ_ID,    d.KDKSBH,a.BRID00,a.ZYH000,c.BRXM00,c.BRXB00,a.HSXMID,a.XMBH00,a.XMMC00,a.XMDJ00, 
	               a.XMSL00,a.HJJE00,d.YJKSBH,b.DQKS00,a.DJID00, SF_XT_CSRQTONL_BZ(b.CSRQ00,'',b.RYRQ00,'','3'),d.ZXRQ00,d.ZXSJ00, '住院','00001' 
	               from ZY_FYMX00 a, ZY_BRXXB0 b, BM_BRXXB0 c, YJ_YW0000 d
	                 where  a.BRID00=c.BRID00
	                 and a.ZYID00=b.ZYID00 
	                 and a.SFLB00<>'3' 
	                 and (nvl(as_KSBH00,0)=0 or nvl(as_KSBH00,0)=-1 or d.YJKSBH=as_KSBH00) 
	                 and a.ZYID00=d.ZYGHID and d.MZZYBZ='1' and a.DJID00=d.YJDJH0 and d.ZXRQ00 >= as_KSRQ00  and d.ZXRQ00 <= as_JSRQ00; 
        
        end if;
     end if; 
     --插入BM_ZLXMTJ表
     if as_TJFS00='1' then
        if as_KSFZTJ='0' then
	          --按收费， 汇总，不显示执行科室
	          --HSXMID,HSXMMC,XMBH00,XMMC00汇总记录
	          insert into BM_ZLXMTJ(ID0000,XSXH00,  MZZYLX,  HSXMID,  HSXMMC,  XMBH00, XMMC00,   DJ0000,              SL0000,             JE0000 )	             
	             select          ad_ID0000,     0,null,a.SFXM04,b.HSXMMC,a.SFXM05,a.SJYSXM,a.SFXM06,sum(nvl(a.SFXM07,0)),sum(nvl(a.SFXM08,0)) 
	                from BM_YYSFTJ a , BM_YYHSXM b
	                where a.SFXM04 = b.HSXMID(+) and a.ID0000=ls_YYSFTJ_ID
	                group by a.SFXM04,b.HSXMMC,a.SFXM05,a.SJYSXM,a.SFXM06  having sum(nvl(a.SFXM08,0))<>0; 
 
	       else
	          --按收费， 汇总，显示执行科室
	          insert into BM_ZLXMTJ(ID0000,XSXH00,MZZYLX,HSXMID,HSXMMC,XMBH00,XMMC00,DJ0000,SL0000,JE0000,ZXKSBH)
	             select ad_ID0000, 0,null,a.SFXM04,b.HSXMMC,a.SFXM05,a.SJYSXM,a.SFXM06,sum(nvl(a.SFXM07,0)),sum(nvl(a.SFXM08,0)),a.SFXM09
	                from BM_YYSFTJ a , BM_YYHSXM b
	                where a.SFXM04 = b.HSXMID(+) and a.ID0000=ls_YYSFTJ_ID
	                group by a.SFXM04,b.HSXMMC,a.SFXM05,a.SJYSXM,a.SFXM06,a.SFXM09 having sum(nvl(a.SFXM08,0))<>0; 
          
	       end if;
	       --小计
	       insert into BM_ZLXMTJ(ID0000,XSXH00,MZZYLX,HSXMID,HSXMMC,XMBH00,XMMC00,DJ0000,SL0000,JE0000)
	             select  ad_ID0000,1,'1',a.HSXMID,'小计：',null,null,null,sum(nvl(a.SL0000,0)),sum(nvl(a.JE0000,0))
	                from BM_ZLXMTJ a where a.ID0000=ad_ID0000 and a.XSXH00=0 
	                group by a.HSXMID;
	       --总计      
	       insert into BM_ZLXMTJ(ID0000,XSXH00,MZZYLX,HSXMID,HSXMMC,XMBH00,XMMC00,DJ0000,SL0000,JE0000)
	             select  ad_ID0000,2,'1',null,'总计：',null,null,null,sum(nvl(a.SL0000,0)),sum(nvl(a.JE0000,0))
	                from BM_ZLXMTJ a where a.ID0000=ad_ID0000 and a.XSXH00=0 ;
	    else	       
	       --按收费， 明细，都不显示执行科室
	       insert into BM_ZLXMTJ(ID0000,XSXH00,MZZYLX,ZYHGHH,BRXM00,XB0000,HSXMID,HSXMMC,XMBH00,XMMC00,DJ0000,SL0000,JE0000,
	          KDKSBH,DQKS00,NL0000,ZXRQ00,ZXSJ00,ZXKSBH)
	          select ad_ID0000, 0, a.BZSM08, a.BZSM01, a.BZSM02, a.BZSM03,a.SFXM04,b.HSXMMC,a.SFXM05,a.SJYSXM,a.SFXM06,sum(nvl(a.SFXM07,0)),sum(nvl(a.SFXM08,0)),
	                a.BMBH00, a.SFXM10, a.BZSM05, a.BZSM06, a.BZSM07,a.SFXM09
	                from BM_YYSFTJ a , BM_YYHSXM b, BM_BRXXB0 c 
	                where a.SFXM01=c.BRID00 and  a.SFXM04 = b.HSXMID(+) and a.ID0000=ls_YYSFTJ_ID
	                group by a.BZSM01, a.BZSM02, a.BZSM03,a.SFXM04,b.HSXMMC,a.SFXM05,a.SJYSXM,a.SFXM06,a.BMBH00, a.SFXM10, a.BZSM05,a.BZSM06, a.BZSM07,a.SFXM09,a.BZSM08 having sum(nvl(a.SFXM08,0))<>0;
	       --小计
	       insert into BM_ZLXMTJ(ID0000,XSXH00,MZZYLX,ZYHGHH,BRXM00,XB0000,HSXMID,HSXMMC,XMBH00,XMMC00,DJ0000,SL0000,JE0000)
	          select ad_ID0000, 1, a.MZZYLX,a.ZYHGHH,a.BRXM00,a.XB0000,null,'小计：',null,null,null,sum(nvl(a.SL0000,0)),sum(nvl(a.JE0000,0)) 
	             from BM_ZLXMTJ a where a.ID0000=ad_ID0000 and a.XSXH00=0 
	             group by a.ZYHGHH,a.BRXM00,a.XB0000,a.MZZYLX;
	             
	       --总计
	       insert into BM_ZLXMTJ(ID0000,XSXH00,MZZYLX,ZYHGHH,BRXM00,XB0000,HSXMID,HSXMMC,XMBH00,XMMC00,DJ0000,SL0000,JE0000)
	          select ad_ID0000, 2, null,null,null,null,null,'总计：',null,null,null,sum(nvl(a.SL0000,0)),sum(nvl(a.JE0000,0)) 
	             from BM_ZLXMTJ a where a.ID0000=ad_ID0000 and a.XSXH00=0 ;
    
	   end if;
  else
     --按诊疗
     if as_RQFS00='0' then    
	       --按记帐日期
	       if as_TJLX00='0' or as_TJLX00='1'then
	         --门诊
	         
	         insert into BM_YYSFTJ(ID0000,BMBH00,  SFXM01,  BZSM01,  BZSM02,  BZSM03,   SFXM05, 
	             SFXM06,                                                       SFXM07,  SFXM08,  SFXM09,  BZSM04, BZSM05,SFXM10,BZSM08)                                     
	            select ls_YYSFTJ_ID,    a.KDKSBH,a.BRID00,a.ZYHGHH,c.BRXM00,c.BRXB00, a.ZLXMID,
	               nvl(a.ZJE000, 0)/decode(a.SL0000, 0, 1, a.SL0000) as DJ0000,a.SL0000,a.ZJE000,a.YJKSBH,to_char(a.YJDJH0),SF_XT_CSRQTONL_BZ(b.CSRQ00,'',b.GHRQ00,'','3'),a.KDKSBH,'门诊'
	               from YJ_YW0000 a,SF_BRXXB0 b, BM_BRXXB0 c, SF_BRFY00 d 
	               where a.ZYGHID=b.GHID00 and a.BRID00=c.BRID00 and a.XMZT00 in ('2','3','4') 
	               and (nvl(as_KSBH00,0)=0 or nvl(as_KSBH00,0)=-1 or a.YJKSBH=as_KSBH00) and a.MZZYBZ='0' 
	               --and exists (select 1 from SF_FYMX00 where MZH000=a.ZYHGHH  and BRID00=a.BRID00 and CZRQ00>= as_KSRQ00  and CZRQ00 <= as_JSRQ00 
	               --     and YJDJH0=to_char(a.YJDJH0) and SFLB00<>'3' );--SF_FYMX00.YJDJH0是varchar,to_char后YJ_YW0000.YJDJH0失效
	               and A.ZYGHID = d.MZID00 and A.SFDJH0 = d.DJH000 and  d.CZRQ00>= as_KSRQ00  and d.CZRQ00 <= as_JSRQ00; 
	       end if;
	       if as_TJLX00='0' or as_TJLX00='2'then 
           --住院
	         insert into BM_YYSFTJ(ID0000,BMBH00,  SFXM01,  BZSM01,  BZSM02,  BZSM03,   SFXM05, 
	             SFXM06,                                                       SFXM07,  SFXM08,  SFXM09,  BZSM04, BZSM05,SFXM10,BZSM08)                                     
	            select ls_YYSFTJ_ID,    a.KDKSBH,a.BRID00,a.ZYHGHH,c.BRXM00,c.BRXB00, a.ZLXMID,
	               nvl(a.ZJE000, 0)/decode(a.SL0000, 0, 1, a.SL0000) as DJ0000,a.SL0000,a.ZJE000,a.YJKSBH,to_char(a.YJDJH0),SF_XT_CSRQTONL_BZ(b.CSRQ00,'',b.RYRQ00,'','3'),a.KDKSBH,'住院'
	               from YJ_YW0000 a,ZY_BRXXB0 b, BM_BRXXB0 c, ZY_BRFY00 d 
	               where a.ZYGHID=b.ZYID00 and a.BRID00=c.BRID00 and a.XMZT00 in ('2','3','4') 
	               and (nvl(as_KSBH00,0)=0 or nvl(as_KSBH00,0)=-1 or a.YJKSBH=as_KSBH00) and a.MZZYBZ='1' 
	               --and exists (select 1 from ZY_FYMX00 where ZYID00=a.ZYGHID  and BRID00=a.BRID00 and CZRQ00>= as_KSRQ00  and CZRQ00 <= as_JSRQ00 
	               --     and DJID00=to_char(a.YJDJH0) and SFLB00<>'3' );
	               and A.ZYGHID = d.ZYID00 and A.SFDJH0 = d.DJH000 and  d.CZRQ00>= as_KSRQ00  and d.CZRQ00 <= as_JSRQ00; 
	       end if;
	    else
	       --按执行日期
	       if as_TJLX00='0' or as_TJLX00='1'then
	         --门诊	         
	         insert into BM_YYSFTJ(ID0000,BMBH00,  SFXM01,  BZSM01,  BZSM02,  BZSM03,   SFXM05, 
	             SFXM06,                                                       SFXM07,  SFXM08,  SFXM09,  BZSM04, BZSM05,SFXM10,BZSM08)                                      
	            select ls_YYSFTJ_ID,    a.KDKSBH,a.BRID00,a.ZYHGHH,c.BRXM00,c.BRXB00, a.ZLXMID,
	             nvl(a.ZJE000, 0)/decode(a.SL0000, 0, 1, a.SL0000) as DJ0000,a.SL0000,a.ZJE000,a.YJKSBH,to_char(a.YJDJH0),SF_XT_CSRQTONL_BZ(b.CSRQ00,'',b.GHRQ00,'','3'),a.KDKSBH,'门诊'
	             from YJ_YW0000 a,SF_BRXXB0 b, BM_BRXXB0 c 
	             where a.ZYGHID=b.GHID00 and a.BRID00=c.BRID00 and a.XMZT00 in ('2','3','4') and a.MZZYBZ='0' and a.ZXRQ00>=as_KSRQ00 and a.ZXRQ00<=as_JSRQ00
	             and (nvl(as_KSBH00,0)=0 or nvl(as_KSBH00,0)=-1 or a.YJKSBH=as_KSBH00); 
	       end if;
	       if as_TJLX00='0' or as_TJLX00='2'then
	         --住院
	         insert into BM_YYSFTJ(ID0000,BMBH00,  SFXM01,  BZSM01,  BZSM02,  BZSM03,   SFXM05, 
	             SFXM06,                                                       SFXM07,  SFXM08,  SFXM09,  BZSM04, BZSM05,SFXM10,BZSM08)                                      
	            select ls_YYSFTJ_ID,    a.KDKSBH,a.BRID00,a.ZYHGHH,c.BRXM00,c.BRXB00, a.ZLXMID,
	             nvl(a.ZJE000, 0)/decode(a.SL0000, 0, 1, a.SL0000) as DJ0000,a.SL0000,a.ZJE000,a.YJKSBH,to_char(a.YJDJH0),SF_XT_CSRQTONL_BZ(b.CSRQ00,'',b.RYRQ00,'','3'),a.KDKSBH,'住院'
	             from YJ_YW0000 a,ZY_BRXXB0 b, BM_BRXXB0 c 
	             where a.ZYGHID=b.ZYID00 and a.BRID00=c.BRID00 and a.XMZT00 in ('2','3','4') and a.MZZYBZ='1' and a.ZXRQ00>=as_KSRQ00 and a.ZXRQ00<=as_JSRQ00
	             and (nvl(as_KSBH00,0)=0 or nvl(as_KSBH00,0)=-1 or a.YJKSBH=as_KSBH00); 
	       end if;
	    end if;
	    --插入BM_ZLXMTJ表
	    if as_TJFS00='1' then 
	       --汇总
	       if as_KSFZTJ='0' then
	          insert into BM_ZLXMTJ(ID0000,XSXH00,ZLXMJC,DJ0000,SL0000,JE0000)		                       
	             select ad_ID0000, 0,b.ZLXMJC,a.SFXM06,sum(nvl(a.SFXM07,0)),sum(nvl(a.SFXM08,0))
	                from BM_YYSFTJ a , BM_ZLZD00 b
	                where a.SFXM05 = b.ZLXMID and a.ID0000=ls_YYSFTJ_ID
	                group by b.ZLXMJC,a.SFXM06 having sum(nvl(a.SFXM08,0))<>0;  
	       else
	          insert into BM_ZLXMTJ(ID0000,XSXH00,ZLXMJC,DJ0000,SL0000,JE0000, ZXKSBH)		                       
	             select ad_ID0000, 0,b.ZLXMJC,a.SFXM06,sum(nvl(a.SFXM07,0)),sum(nvl(a.SFXM08,0)), a.SFXM09
	                from BM_YYSFTJ a , BM_ZLZD00 b
	                where a.SFXM05 = b.ZLXMID and a.ID0000=ls_YYSFTJ_ID
	                group by b.ZLXMJC, a.SFXM06, a.SFXM09 having sum(nvl(a.SFXM08,0))<>0;	 
	       
	       end if;
	       
	       --总计
	       insert into BM_ZLXMTJ(ID0000,XSXH00,MZZYLX,ZLXMJC,DJ0000,SL0000,JE0000)	             
	          select          ad_ID0000,     2,   '1','总计：',null,sum(nvl(a.SL0000,0)),sum(nvl(a.JE0000,0))
	             from BM_ZLXMTJ a 
	             where a.ID0000=ad_ID0000 and a.XSXH00=0 ;

	   else
	      --明细，都不显示执行科室
	      insert into BM_ZLXMTJ(ID0000,XSXH00,MZZYLX,ZYHGHH,  BRXM00,  XB0000,  ZLXMJC,  DJ0000,SL0000,JE0000,
	          KDKSBH,DQKS00,NL0000)
	          select  ad_ID0000, 0,a.BZSM08,            a.BZSM01,a.BZSM02,a.BZSM03,b.ZLXMJC,a.SFXM06,sum(nvl(a.SFXM07,0)),sum(nvl(a.SFXM08,0)),
	             a.BMBH00,a.SFXM10,a.BZSM05
	             from BM_YYSFTJ a , BM_ZLZD00 b
	                where a.SFXM05 = b.ZLXMID and a.ID0000=ls_YYSFTJ_ID
	                group by a.BZSM01,a.BZSM02,a.BZSM03,b.ZLXMJC,a.SFXM06,a.SFXM10,a.BZSM05,a.BMBH00,a.BZSM08 having sum(nvl(a.SFXM08,0))<>0;	
	      --小计         
	      insert into BM_ZLXMTJ(ID0000,XSXH00,MZZYLX,ZYHGHH,  BRXM00,  XB0000,ZLXMJC,DJ0000,SL0000,JE0000 )  
	          select ad_ID0000, 1,a.MZZYLX,             a.ZYHGHH,a.BRXM00,a.XB0000,'小计：',null,sum(nvl(a.SL0000,0)),sum(nvl(a.JE0000,0))
	             from BM_ZLXMTJ a 
	             where a.ID0000=ad_ID0000 and a.XSXH00=0 
	             group by a.ZYHGHH,a.BRXM00,a.XB0000,a.MZZYLX;       
	      --总计 
	      insert into BM_ZLXMTJ(ID0000,XSXH00,MZZYLX,ZYHGHH,BRXM00,XB0000,ZLXMJC,DJ0000,SL0000,JE0000)  
	          select ad_ID0000, 2,null,null,null,null,'总计：',null,sum(nvl(a.SL0000,0)),sum(nvl(a.JE0000,0))
	             from BM_ZLXMTJ a 
	             where a.ID0000=ad_ID0000 and a.XSXH00=0 ;           
	
	   end if;	    	   	    
  end if;
  
  delete BM_YYSFTJ where ID0000=ls_YYSFTJ_ID;
  
  --commit;      YJ9-20240903-001
exception
    when others then
           AD_YHMSG0:='出错原因不详,请记录此信息并和系统管理员联系！';
           AD_SYSMSG:=substr(SQLERRM||'执行存储过程错误:SP_YJ_ZLXMTJ('||as_KSRQ00||','||as_JSRQ00||
                       ','||to_char(AS_KSBH00)||'ad_ID0000,ad_YHMSG0,ad_SYSMSG',1,200);
           rollback;
end;
/
show error;
--%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%