CREATE OR REPLACE PROCEDURE SP_XK_XYCKHZ(
  PTJ0000 in varchar2          --查询条件
)
as
-- Person      Date        Comments
-- wusd        2019.03.16  create for SXGL9-20190222-001  
  V_SQL000 varchar2(1000);
begin   
  delete XK_XYCKHZ;
  delete XK_XYCKHZ_TMP;
  V_SQL000 := ' insert into XK_XYCKHZ_TMP(XYPZMC,XX0000,XDBH00,CKSL00,CKDW00,DJ0000,JE0000,FJJE00,SQKS00)
                  select XYPZMC,XX0000,XDBH00,CKSL00,CKDW00,DJ0000,JE0000,         
                         nvl((select FJJE00 from BM_TYZD00 A,BM_XYXX00 B              
                              where A.NBBH00=B.NBBH00 and A.MC0000=X.XYPZMC),0) FJJE00,
                         nvl((SELECT BMBH00 FROM BM_BMBM00 WHERE BMMC00=(select KSMC00 from XK_SXSQ00 where SQDH00=X.SQDH00)),0) SQKS00                 
                  from XK_XYCK00 X            
                  where CKZT00=''1'' and CXBZ00=''Z'' '||PTJ0000;
  SP_EXECUTE_SQL(V_SQL000);  
  insert into XK_XYCKHZ(XYPZMC,XX0000,CKSL00,CKDW00,DJ0000,JE0000,FJJE00,ZJE000,SQKSMC)
    select XYPZMC,XX0000,CKSL00,CKDW00,DJ0000,JE0000,FJJE00,FJJE00+JE0000 ZJE000,SQKSMC
    from(select XYPZMC,XX0000,CKDW00,DJ0000,sum(CKSL00) CKSL00,sum(JE0000) JE0000,
                FJJE00*(select nvl(sum(nvl(CSL000,0)),0) CSL000
                        from(select SQKS00,XYPZMC,XX0000,XDBH00,count(*) CSL000     
                             from(select distinct SQKS00,XYPZMC,XX0000,XDBH00
                                  from XK_XYCKHZ_TMP)
                             group by SQKS00,XYPZMC,XX0000,XDBH00)
                        where SQKS00=X.SQKS00 and XYPZMC=X.XYPZMC and XX0000=X.XX0000) FJJE00,
                (select trim(BMMC00) from BM_BMBM00 where BMBH00=X.SQKS00) SQKSMC                       
         from XK_XYCKHZ_TMP X  
         group by SQKS00,XYPZMC,XX0000,CKDW00,DJ0000,FJJE00);
  commit;   
exception 
  when others then   
     rollback;
    raise_application_error(-20011,substr(SQLERRM||'执行过程SP_XK_XYCKHZ',1,240));
end;