declare
 lvbbid00 BB_FR3BB0.BBID00%TYPE;
 lvcount0 number(5);
 lvxtdm00 XT_XTMK00.XTDM00%TYPE;
 lvbbname BB_FR3BB0.BBNAME%TYPE;
 lvmblx00 BB_FR3BB0.MBLX00%TYPE;
 lvsqllx0 BB_FR3BB0.SQLLX0%TYPE;
 lvcjczy0 BB_FR3BB0.CJCZY0%TYPE;
 lvbbbh00 BB_FR3BB0.BBBH00%TYPE;
 lvzt0000 BB_FR3BB0.ZT0000%TYPE;
 lvbblxmc BB_MBLX00.MBMC00%TYPE;
 lvyclpro BB_FR3BB0.YCLPRO%TYPE;
Begin
 select 65 into lvbbid00 from dual ;
 select '0' into lvxtdm00 from dual ;
 select '门诊科室收入日报表' into lvbbname from dual;
 select 0 into lvsqllx0 from dual ;
 select '实达系统' into lvcjczy0 from dual ;
 select '' into lvbbbh00 from dual ;
 select '0' into lvzt0000 from dual ;
 select '科室收入' into lvbblxmc from dual ;
 select 'SP_SF_MZKSSR_RBB000_LY' into lvyclpro from dual ;
 select count(*) into lvcount0 from BB_FR3BB0 WHERE BBNAME=lvbbname;
 if lvcount0>0 then 
   select BBID00 INTO lvbbid00 from BB_FR3BB0 WHERE  BBNAME=lvbbname; 
 else 
   select SQ_BB_FR3BB0_BBID00.nextval into lvbbid00 from dual;
 end if;
 select count(*) into lvcount0 from BB_MBLX00 where MBMC00=lvbblxmc ;
 if lvcount0>0 then 
   select MBLX00 INTO lvmblx00 from BB_MBLX00 WHERE MBMC00=lvbblxmc  ; 
 else 
   select SQ_BB_MBLX00_MBLX00.nextval into lvmblx00 from dual;
   Insert into BB_MBLX00 (MBLX00, MBBH00, MBMC00)
     values(lvmblx00,'000000',lvbblxmc); 
 end if;
 delete TEMP_BBID00;
 insert into TEMP_BBID00(BBID00) values(lvbbid00);
 delete BB_FR3BB0 where BBID00=lvbbid00;
 insert into BB_FR3BB0(BBID00,BBNAME,MBLX00,CJCZY0,CRDATE,SQLLX0,BBBH00,ZT0000,YCLPRO)
 values(lvbbid00,lvbbname,lvmblx00,lvcjczy0,to_char(sysdate,'YYYYMMDD'),lvsqllx0,lvbbbh00,lvzt0000,lvyclpro);
 delete XT_XTMK00 where XTDM00=lvxtdm00 and trim(MKDM00)=trim(lvxtdm00)||'@'||to_char(lvbbid00);  
 insert into XT_XTMK00(MKDM00,MKNBMC,MKWBMC,XTDM00)
 Values(trim(lvxtdm00)||'@'||to_char(lvbbid00),'REPORT',lvbbname,lvxtdm00);
-------BB_CSMX00--------
delete BB_CSMX00 where BBID00=lvbbid00;
 insert into BB_CSMX00 (BBID00, XH0000, CSMC00, CSZLX0, CSBJLX, CSZSQL,NAME00,CSWBMC,SFXTCS,WBXH00) 
 values(lvbbid00,SQ_BB_CSMX00_XH0000.nextval,'AD_ID0000',2,1,'','select A.BMBH00,b.BMMC00,A.XMHJ00,A.SFXM99,A.SFXM0','AD_ID0000','2',7);
 insert into BB_CSMX00 (BBID00, XH0000, CSMC00, CSZLX0, CSBJLX, CSZSQL,NAME00,CSWBMC,SFXTCS,WBXH00) 
 values(lvbbid00,SQ_BB_CSMX00_XH0000.nextval,'开始日期',3,3,'','select A.BMBH00,b.BMMC00,A.XMHJ00,A.SFXM99,A.SFXM0','开始日期','0',8);
 insert into BB_CSMX00 (BBID00, XH0000, CSMC00, CSZLX0, CSBJLX, CSZSQL,NAME00,CSWBMC,SFXTCS,WBXH00) 
 values(lvbbid00,SQ_BB_CSMX00_XH0000.nextval,'结束日期',3,3,'','select A.BMBH00,b.BMMC00,A.XMHJ00,A.SFXM99,A.SFXM0','结束日期','0',9);
-------BB_YCLPCS---------
 delete BB_YCLPCS where BBID00=lvbbid00; 
insert into BB_YCLPCS(BBID00,XH0000,CSMC00,CSLX00,OUTBZ0,CSZ000)
 Values(lvbbid00,0,'AS_KSRQ00','CHAR','IN',':开始日期');
insert into BB_YCLPCS(BBID00,XH0000,CSMC00,CSLX00,OUTBZ0,CSZ000)
 Values(lvbbid00,1,'AS_JSRQ00','CHAR','IN',':结束日期');
insert into BB_YCLPCS(BBID00,XH0000,CSMC00,CSLX00,OUTBZ0,CSZ000)
 Values(lvbbid00,2,'AD_ID0000','NUMBER','OUT','');
-------BB_SQL000--------
delete BB_SQL000 where BBID00=lvbbid00;
 insert into BB_SQL000(BBID00,XH0000,SQL000) values (lvbbid00,SQ_BB_SQL000_XH0000.nextval,'select A.BMBH00,b.BMMC00,A.XMHJ00,A.SFXM99,(A.SFXM01+A.SFXM02)SFXM01,A.SFXM03,A.SFXM04,A.SFXM05,A.SFXM06,A.SFXM07,A.SFXM08

,A.SFXM09,A.SFXM10,A.SFXM11,A.SFXM12,A.SFXM13,A.SFXM14,A.SFXM15,A.SFXM16,A.SFXM17,A.SFXM18,A.SFXM19,A.SFXM20,A.SFXM21,A.SFXM22,A.SFXM23,A.SFXM24,A.SFXM25,A.SFXM26,A.SFXM27,A.SFXM28,A.SFXM29,A.SFXM30,A.SFXM31 from  BM_YYSFTJ A,BM_BMBM00 B  where A.BMBH00 = B.BMBH00(+) and A.ID0000 =:AD_ID0000 and a.bmbh00<>999999999 and a.bmbh00 in (select bmbh00 from bm_bmbm00 where bmmc00 in (''门五官科'',''门口腔科'',''门中医科'',''门皮肤科'',''门妇产科'',''门康复科'',''门诊收费处'')) union all select 0,''门诊'' BMMC00,sum(A.XMHJ00),sum(A.SFXM99),(sum(A.SFXM01)+sum(A.SFXM02)) SFXM01,sum(A.SFXM03),sum(A.SFXM04),sum(A.SFXM05),sum(A.SFXM06),sum(A.SFXM07),sum(A.SFXM08),sum(A.SFXM09),sum(A.SFXM10),sum(A.SFXM11),sum(A.SFXM12),sum(A.SFXM13),sum(A.SFXM14),sum(A.SFXM15),sum(A.SFXM16),sum(A.SFXM17),sum(A.SFXM18),sum(A.SFXM19),sum(A.SFXM20),sum(A.SFXM21),sum(A.SFXM22),sum(A.SFXM23),sum(A.SFXM24),sum(A.SFXM25),sum(A.SFXM26),sum(A.SFXM27),sum(A.SFXM28),sum(A.SFXM29),sum(A.SFXM30),sum(A.SFXM31) from  BM_YYSFTJ A where  A.ID0000 =:AD_ID0000 and a.bmbh00 not in (select bmbh00 from bm_bmbm00 where bmmc00 in (''门五官科'',''门口腔科'',''门中医科'',''门皮肤科'',''门妇产科'',''门康复科'',''门诊收费处'')) and a.bmbh00<>999999999 union all select 0,''总计''BMMC00,A.XMHJ00,A.SFXM99,

(A.SFXM01+A.SFXM02)SFXM01,A.SFXM03,A.SFXM04,A.SFXM05,A.SFXM06,A.SFXM07,A.SFXM08,A.SFXM09,A.SFXM10,A.SFXM11,A.SFXM12,A.SFXM13,A.SFXM14,A.SFXM15,A.SFXM16,A.SFXM17,A.SFXM18,A.SFXM19,A.SFXM20,A.SFXM21,A.SFXM22,A.SFXM23,A.SFXM24,A.SFXM25,A.SFXM26,A.SFXM27,A.SFXM28,A.SFXM29,A.SFXM30,A.SFXM31 from BM_YYSFTJ A where A.ID0000 =:AD_ID0000 and a.bmbh00=999999999
























































');
end ;
----end----
update BB_FR3BB0 SET BBDATA=:bbdata where  BBID00=(select BBID00 from TEMP_BBID00)