CREATE OR REPLACE PROCEDURE SP_XK_CKKSHZ(
  PTJ0000 in varchar2          --查询条件
)
as
-- Person      Date        Comments
-- wusd        2019.03.16  create for SXGL9-20190222-001  
  V_SQL000 varchar2(1000);
begin   
  delete XK_CKKSHZ;
  delete XK_CKKSHZ_TMP;
  V_SQL000 := ' insert into XK_CKKSHZ_TMP(XYPZMC,XDBH00,CKSL00,CKDW00,DJ0000,JE0000,FJJE00,SQKS00)
                  select XYPZMC,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_CKKSHZ(XYPZMC,CKSL00,CKDW00,DJ0000,JE0000,FJJE00,ZJE000,SQKS00,SQKSMC)
    select XYPZMC,CKSL00,CKDW00,DJ0000,JE0000,FJJE00,FJJE00+JE0000 ZJE000,SQKS00,SQKSMC
    from(select XYPZMC,CKDW00,DJ0000,sum(CKSL00) CKSL00,sum(JE0000) JE0000,
                FJJE00*(select nvl(sum(nvl(CSL000,0)),0) CSL000
                        from(select SQKS00,XYPZMC,XDBH00,count(*) CSL000     
                             from(select distinct SQKS00,XYPZMC,XDBH00
                                  from XK_CKKSHZ_TMP)
                             group by SQKS00,XYPZMC,XDBH00)
                        where SQKS00=X.SQKS00 and XYPZMC=X.XYPZMC) FJJE00,
                SQKS00,(select trim(BMMC00) from BM_BMBM00 where BMBH00=X.SQKS00) SQKSMC                       
         from XK_CKKSHZ_TMP X  
         group by SQKS00,XYPZMC,CKDW00,DJ0000,FJJE00);
  insert into XK_CKKSHZ(XYPZMC,CKSL00,CKDW00,DJ0000,JE0000,FJJE00,ZJE000,SQKS00,SQKSMC)       
    select '' XYPZMC,sum(CKSL00) CKSL00,'' CKDW00,'' DJ0000,sum(JE0000) JE0000,sum(FJJE00) FJJE00, 
           sum(JE0000)+sum(FJJE00) ZJE000,to_char(SQKS00) SQKS00,'         小计' SQKSMC
    from XK_CKKSHZ_TMP
    group by SQKS00;
  insert into XK_CKKSHZ(XYPZMC,CKSL00,CKDW00,DJ0000,JE0000,FJJE00,ZJE000,SQKS00,SQKSMC)  
    select '' XYPZMC,sum(CKSL00) CKSL00,'' CKDW00,'' DJ0000,sum(JE0000) JE0000,sum(FJJE00) FJJE00, 
           sum(JE0000)+sum(FJJE00) ZJE000,'' SQKS00,'         合计' SQKSMC
    from XK_CKKSHZ_TMP;                      
  commit;   
exception 
  when others then   
     rollback;
    raise_application_error(-20011,substr(SQLERRM||'执行过程SP_XK_CKKSHZ',1,240));
end;