CREATE OR REPLACE PROCEDURE SP_ZY_YBZDCFCW 
(
       ad_ZYID00     in NUMBER
)
as
       ls_BRID00       BM_BRXXB0.BRID00%TYPE;   --病人ID
       ls_YBBRLB       BM_BRXXB0.YBBRLB%TYPE;   --医保病人类别
       ls_YBLB00       BM_BRXXB0.YBLB00%TYPE;   --医保类别
       ls_TYDWMC       BM_BRXXB0.TYDWMC%TYPE;   --病人单位
       ls_FBBH00       BM_BRXXB0.FBBH00%TYPE;   --病人费别
       ls_CWXMBH       BM_YBBRLB.CWXMBH%type;   --医保中心为各类病人指定新增的床位项目编号     
       ls_BRLBMC       BM_YBBRLB.BRLBMC%type;   --医保病人类别
       ls_YBZXLB       BM_YBFPXM.YBZXLB%TYPE;   --医保中心类别编码
       ls_YBXMMC       BM_YBSFXM.XMMC00%TYPE;   --医保收费/药品项目名称
       ls_YBXMBH       BM_YBSFXM.XMBH00%TYPE;   --医保收费/药品项目编号
       ls_YBXMSX       BM_YBSFXM.SFSX00%TYPE;   --医保项目收费上限
       ls_SFYBXM       BM_YBSFDY.SFYBXM%TYPE;   --是否医保项目
       ls_YBXMLB       BM_YBSFDY.YBLB00%TYPE;   --医保项目类别
       ls_ID0000       BM_ZYCF00_TEMP00.ID0000%TYPE;

       ls_YBZFBL       ZY_FYMX00.ZFBL00%TYPE;   --医保自付比例
       ls_XMMC00       ZY_FYMX00.XMMC00%TYPE;   --费用项目名称

       ls_COUNT0       NUMBER(4);
       ls_GJBM00       BM_YYSFXM.XMMC00%TYPE;   --国家编码
       ls_SFXMID       BM_YYSFXM.SFXMID%TYPE;   --拆分床位ID
       ls_CBCWID       BM_YYSFXM.SFXMID%TYPE;   --超标床位ID
       ls_SFJE00       BM_YYSFXM.SFJE00%TYPE;   --拆分床位价格
       CURSOR CUR_ZY_FYMX00_MXID01 IS
             select a.MXID00,decode(a.JMBZ00,'4',a.ZFJE00,a.ZFJE00-a.JMJE00) ZFJE00,substrb(c.XMMC00,1,50) XMMC00,INSTRB(a.XMMC00,'(包)')BCBZ00,--包床标志
                    substrb(nvl(c.GG0000,'无'),1,10) GG0000,nvl(c.DW0000,'无')DW0000,a.XMBH00,
                    decode(a.JMBZ00,'4',(a.HJJE00+a.JMJE00),(a.HJJE00-a.JMJE00))/decode(a.XMSL00,0,1,a.XMSL00) XMDJ00,a.XMSL00,decode(a.JMBZ00,'4',(a.HJJE00+a.JMJE00),(a.HJJE00-a.JMJE00)) HJJE00,a.ZFBL00
             from   ZY_FYMX00 A,BM_FPXM00 B,BM_YYSFXM C,ZY_BRFY00 D
             where  a.XMBH00 = C.SFXMID  and C.ZYFPID = B.FPXMID and D.DJH000=a.DJH000
                    and B.SFYLXM='Y' and a.XMBH00<9999999900  --将西药,草药,成药,材料的去掉(以及将专门减免的也去掉 jlg)
                    and d.ZYID00=ad_ZYID00 and a.JMBZ00<>'2'
                    and d.jzdh00+0=0; 
-- Person      Date         Comments     
-- yangy       2008.09.24   医保病人自动拆分床位项目
-- zhr         2010.05.30   医保病人类别为'财政核拨离休干部','破产企业离休人员' ,'特困企业离休人员'时，当病人单位含厅级，按"床位费（地市级医院离休厅级）"发送，否则按"床位费（地市级医院离休处级）"
BEGIN
	
    select  BRID00 into ls_BRID00 from ZY_BRXXB0 where  ZYID00=ad_ZYID00;
    select YBBRLB,FBBH00,YBLB00,TYDWMC into ls_YBBRLB,ls_FBBH00,ls_yblb00,ls_TYDWMC from BM_BRXXB0 where BRID00 = ls_BRID00;
    if ls_FBBH00 <>3 then 
       return;
    end if;     
    --取医保中心类别
    BEGIN
      select YBZXLB into ls_YBZXLB from IC_YBBRLB where (FBBH00,YBLB00)=(select FBBH00,YBLB00 from BM_BRXXB0 where brid00=ls_BRID00);
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
            ROLLBACK;
            RETURN;
    END;	
    
	
    --是否拆分床位费用
    select to_number(nvl(max(trim(VALUE0)),'0'))  into ls_CBCWID   from  XT_XTCS00  where  NAME00 = 'ZY_CBCWSFXMID';   --超标床位收费项目ID
    if ls_CBCWID=0 then
       return;
    end if;
  

    --能报销床位的项目
    select  trim(max(CWXMBH)),max(BRLBMC)  into ls_CWXMBH,ls_BRLBMC from  bm_ybbrlb  where  fbbh00=ls_FBBH00 and YBZXLB = ls_YBZXLB and YBBRLB = ls_YBBRLB;
    if ls_CWXMBH is null then
       return;
    else
       --2010.05.30 zhr 医保病人类别为'财政核拨离休干部','破产企业离休人员' ,'特困企业离休人员'时，当病人单位含厅级，按"床位费（地市级医院离休厅级）"发送，否则按"床位费（地市级医院离休处级）"
       if ls_YBZXLB='1' and ls_BRLBMC in ('财政核拨离休干部','破产企业离休人员' ,'特困企业离休人员')
          and instr(ls_TYDWMC,'厅级')>0 and ls_CWXMBH='711090000539' then 
          ls_CWXMBH:='711090000537';
       end if;
       select SFXMID,SFJE00,XMMC00,GJBM00 into ls_SFXMID,ls_SFJE00,ls_XMMC00,ls_GJBM00 from BM_YYSFXM where BH0000=trim(ls_CWXMBH);
       for mx in CUR_ZY_FYMX00_MXID01 loop
           --如果项目
           if (mx.XMBH00<>ls_SFXMID) and (mx.BCBZ00<=0) then
              if (ls_CWXMBH is not null) and (instrb(mx.XMMC00,'床位')>0) and (instrb(mx.XMMC00,'监护病房床位费')<=0) then
                 select count(*) into ls_COUNT0 from BM_CWJM00 where SFXMID=mx.XMBH00;
                 if ls_COUNT0>0 then
                     select  D.SFYBXM,nvl(D.SFSX00,0),D.XMMC00,D.XMBH00,D.ZFBL00,D.YBLB00
                         into  ls_SFYBXM,ls_YBXMSX,ls_YBXMMC,ls_YBXMBH,ls_YBZFBL,ls_YBXMLB
                         from  BM_YBSFXM D where D.XMBH00 = ls_CWXMBH and d.YBZXLB = ls_YBZXLB;
                     if (mx.XMDJ00>ls_YBXMSX) and (ls_YBXMSX>0) and (ls_SFYBXM='Y') then
                        --插入差价项目
                        insert into ZY_FYMX00 (MXID00,DJH000,XMBH00,XMMC00,XMGG00,XMDW00,XMDJ00,XMSL00,
                                         HJJE00,ZFJE00,GFJE00,JZJE00,JMJE00,
                                         JMSHR0,JMYY00,JMBZ00,CZRQ00,CZSJ00,ZXRQ00,ZXSJ00,SFYDJ0,CXBZ00,KDKS00,YSKS00,KDYS00,KDBQ00,
                                         ZXKS00,ZXYS00,SFLSCF,BEIZHU,ZFBL00,QZFJE0,GJBM00,SFLB00,YJJZID,FLAG00,DJID00)
                           select SQ_ZY_FYMX00_MXID00.nextval,a.DJH000,b.SFXMID,b.XMMC00,b.GG0000,b.DW0000,(a.XMDJ00-ls_SFJE00),a.XMSL00,
                                         round((a.XMDJ00-ls_SFJE00)*a.XMSL00,2),round((a.XMDJ00-ls_SFJE00)*a.XMSL00,2),0,0,0,
                                         a.JMSHR0,a.JMYY00,a.JMBZ00,a.CZRQ00,a.CZSJ00,a.ZXRQ00,a.ZXSJ00,a.SFYDJ0,a.CXBZ00,a.KDKS00,a.YSKS00,a.KDYS00,a.KDBQ00,
                                         a.ZXKS00,a.ZXYS00,a.SFLSCF,a.BEIZHU,1,a.QZFJE0,b.GJBM00,a.SFLB00,a.YJJZID,a.FLAG00,a.DJID00
                           from ZY_FYMX00 a,BM_YYSFXM b where MXID00=mx.MXID00 and SFXMID=ls_CBCWID;                 
                        --修改原价格     
                        update ZY_FYMX00 set XMDJ00=ls_SFJE00,HJJE00=round(ls_SFJE00*XMSL00,2),ZFJE00=round(ls_SFJE00*XMSL00,2) where MXID00=mx.MXID00;                 
                     end if;
                     update ZY_FYMX00 set ZFBL00=ls_YBZFBL where MXID00=mx.MXID00;                         
                 end if;        
              end if;
                                                   

           end if;
       end loop;
    end if;   

END;
/