drop proc p280_xxx1
go
create procedure p280_xxx1
/*
  -----------------------------------------------------------------------
      P280_XXX1 - IAS Audit Trail Information.
  -----------------------------------------------------------------------
*/

/*
    ------  INPUT VARIABLES   ------
*/
   @rpt   char(3)  = "01."
 , @user  char(10) = "%" 
as
/*
    ------ INTERNAL VARIABLES ------
*/
declare @user2 char(8) 

if @user = " " select @user = '%'

if @rpt = '02.' and @user != '%'
   select @user2 = @user

if @rpt = '02.' 
   goto Report02   

if @rpt = '03.' 
   goto Report03   

if @rpt = '04.' 
   goto Report04   

if @rpt = '05.' 
   goto Report05   

if @rpt = '06.' 
   goto Report06   

if @rpt = '07.' 
   goto Report07   

if @rpt = '08.' 
   goto Report08   

if @rpt = '09.' 
   goto Report09   

if @rpt = '10.' 
   goto Report10   

if @rpt = '11.' 
   goto Report11   

if @rpt = '12.' 
   goto Report12   

if @rpt = '13.' 
   goto Report13   

if @rpt = '14.' 
   goto Report14   


Report02:  

begin
select ' AVERAGE'
     , @user2
     , convert(decimal(15,3),(avg(db_upd_t) / 1000)) 
     , count(*) 
     , getdate()
  from t280audit
 where db_upd_t is not null
   and a_uid_c like @user
union all
select f_srt_c 
     , @user2
     , convert(decimal(15,3),(avg(db_upd_t) / 1000))
     , count(*) 
     , getdate()
  from t280audit
 where db_upd_t is not null
   and a_uid_c like @user
 group by f_srt_c
 order by 1  
end
  goto ENDIT 


Report03:  

begin
select f_srt_c 
     , ' ' 
     , convert(decimal(15,3),(avg(db_upd_t) / 1000)) 
     , count(*)	
     , getdate()
  from t280audit	
 where a_uid_c like @user
 group by f_srt_c  
union all
select '# of Queries'
     , ' ' 
     , convert(decimal(15,3),(avg(db_upd_t) / 1000)) 
     , count(*)	
     , getdate()
  from t280audit	
 where a_uid_c like @user
order by 4 desc
end
  goto ENDIT 


Report04:  

begin
select convert(char(10),a.db_upd_d,101)
     , ' ' 
     , convert(decimal(15,3),(avg(db_upd_t) / 1000)) 
     , count(*)	
     , getdate()
  from t280audit a
 group by convert(char(10),a.db_upd_d,101)
 order by 1 desc
end
  goto ENDIT 


Report05:  

begin
select convert(char(10),a.db_upd_d,101)
     , ' ' 
     , convert(decimal(15,3),(avg(db_upd_t) / 1000)) 
     , count(*)	
     , getdate()
  from t280audit a
 group by convert(char(10),a.db_upd_d,101)
 order by 4 desc
end
  goto ENDIT 


Report06:  

begin
select a.a_uid_c  
     , substring(b.a_uid_x,1,30) 
     , convert(decimal(15,3),(avg(db_upd_t) / 1000)) 
     , count(*)
     , getdate()
  from t280audit a
     , t280users b
 where a.a_uid_c = b.a_uid_c
 group by a.a_uid_c
     , substring(b.a_uid_x,1,30) 
 order by 1
end
  goto ENDIT 


Report07:  

begin
select a.a_uid_c  
     , substring(b.a_uid_x,1,30) 
     , convert(decimal(15,3),(avg(db_upd_t) / 1000)) 
     , count(*)
     , getdate()
  from t280audit a
     , t280users b
 where a.a_uid_c = b.a_uid_c
 group by a.a_uid_c
     , substring(b.a_uid_x,1,30) 
union all
select 'TOTAL'  
     , 'Number of Queries'
     , convert(decimal(15,3),(avg(db_upd_t) / 1000)) 
     , count(*)
     , getdate()
  from t280audit 
 order by 4 desc, 1    
end
  goto ENDIT 


Report08:  

begin
select a.a_uid_c  
     , substring(b.a_uid_x,1,30)
     , convert(decimal(15,3),(avg(db_upd_t) / 1000)) 
     , count(*)
 /*  , a.db_upd_d */
     , convert(char(10),a.db_upd_d,101)
  from t280audit a
     , t280users b
 where a.a_uid_c = b.a_uid_c
   and a.a_uid_c like @user
 group by a.a_uid_c
     , substring(b.a_uid_x,1,30) 
     , convert(char(10),a.db_upd_d,101)
union all
select a.a_uid_c   
     , '-- TOTAL FOR LOGON ID -- '
     , convert(decimal(15,3),(avg(db_upd_t) / 1000)) 
     , count(*)
     , getdate()
  from t280audit a
     , t280users b
 where a.a_uid_c = b.a_uid_c
   and a.a_uid_c like @user
 group by a.a_uid_c
 order by 1, 5 desc
end
  goto ENDIT 


Report09:  

if @user = '%' select @user = '1/1/95'

begin
select a.f_srt_c   
     , a.a_uid_c+substring(b.a_uid_x,1,20) 
     , a.db_upd_t / 1000
     , 0
     , a.db_upd_d 
  from t280audit a
     , t280users b
 where a.a_uid_c = b.a_uid_c
   and a.db_upd_d >= @user
 order by 5 desc, 1
end
  goto ENDIT 


Report10:  

begin
select '==============' ,'=============================='        
     , db_upd_t / 1000, 0, db_upd_d
  from t280audit         where a_uid_c  != ' ' 
and a_uid_c like @user
union all
select '01. SORT ' ,f_srt_c  
     , db_upd_t / 1000, 0, db_upd_d
  from t280audit         where f_srt_c  != '%' 
and a_uid_c like @user
union all
select '01. PROF ' ,substring(f_prof_x,1,30)      
     , db_upd_t / 1000, 0, db_upd_d
  from t280audit         where f_prof_x != ' ' 
and a_uid_c like @user
union all
select '02. BEGPD' ,f_fypd1_c          
     , db_upd_t / 1000, 0, db_upd_d
  from t280audit         where f_fypd1_c != '%' 
and a_uid_c like @user
union all
select '03. ENDPD' ,f_fypd2_c         
     , db_upd_t / 1000, 0, db_upd_d
  from t280audit         where f_fypd2_c != '%' 
and a_uid_c like @user
union all
select '04. LOC  ' ,f_loc_c          
     , db_upd_t / 1000, 0, db_upd_d
  from t280audit         where f_loc_c != '%' 
and a_uid_c like @user
union all
select '05. DIV  ' ,f_div_c         
     , db_upd_t / 1000, 0, db_upd_d
  from t280audit         where f_div_c != '%' 
and a_uid_c like @user
union all
select '06. DPT  ' ,f_dpt_c        
     , db_upd_t / 1000, 0, db_upd_d
  from t280audit         where f_dpt_c != '%' 
and a_uid_c like @user
union all
select '07. TYPE ' ,f_invtyp_c    
     , db_upd_t / 1000, 0, db_upd_d
  from t280audit         where f_invtyp_c != 'ALL' 
and a_uid_c like @user
union all
select '08. SRC  ' ,f_syssrc_c   
     , db_upd_t / 1000, 0, db_upd_d
  from t280audit         where f_syssrc_c != '%' 
and a_uid_c like @user
union all
select '09. STG  ' ,f_stg_c     
     , db_upd_t / 1000, 0, db_upd_d
  from t280audit         where f_stg_c != '%' 
and a_uid_c like @user
union all
select '10. FOP  ' ,f_fop_c    
     , db_upd_t / 1000, 0, db_upd_d
  from t280audit         where f_fop_c != '%' 
and a_uid_c like @user
union all
select '11. FAM  ' ,f_afm_c   
     , db_upd_t / 1000, 0, db_upd_d
  from t280audit         where f_afm_c != '%' 
and a_uid_c like @user
union all
select '13. NSID ' ,i_nsid_c            
     , db_upd_t / 1000, 0, db_upd_d
  from t280audit         where i_nsid_c != '%' 
and a_uid_c like @user
union all
select '14. SPEC ' ,i_spec_c           
     , db_upd_t / 1000, 0, db_upd_d
  from t280audit         where i_spec_c != '%' 
and a_uid_c like @user
union all
select '15. ACCT ' ,f_acct_c          
     , db_upd_t / 1000, 0, db_upd_d
  from t280audit         where f_acct_c != '%' 
and a_uid_c like @user
union all
select '16. EXPR ' ,f_expr_x         
     , db_upd_t / 1000, 0, db_upd_d
  from t280audit         where f_expr_x != '01. None'
and a_uid_c like @user
union all
select '17. ORDER' ,f_ord_x         
     , db_upd_t / 1000, 0, db_upd_d
  from t280audit         where f_ord_x != '01. Category'
and a_uid_c like @user
union all
select '18. GRP  ' ,f_grp_x        
     , db_upd_t / 1000, 0, db_upd_d
  from t280audit         where f_grp_x != '%' 
and a_uid_c like @user
union all
select '19. DVN  ' ,f_div_x       
     , db_upd_t / 1000, 0, db_upd_d
  from t280audit         where f_div_x != '%' 
and a_uid_c like @user
union all
select '20. BUS  ' ,f_bu_x       
     , db_upd_t / 1000, 0, db_upd_d
  from t280audit         where f_bu_x != '%' 
and a_uid_c like @user
union all
select '21. AMT  ' ,f_inv_a            
     , db_upd_t / 1000, 0, db_upd_d
  from t280audit         where f_inv_a != '0.00'
and a_uid_c like @user
union all
select '22. GLVAL' ,f_glval_i         
     , db_upd_t / 1000, 0, db_upd_d
  from t280audit         where f_glval_i != '%' 
and a_uid_c like @user
order by 5 desc
end
  goto ENDIT 


Report11:  

begin
select a.f_srt_c   
     , a.a_uid_c+substring(b.a_uid_x,1,20) 
     , a.db_upd_t / 1000
     , 0
     , a.db_upd_d 
  from t280audit a
     , t280users b
 where a.a_uid_c = b.a_uid_c
   and substring(a.f_srt_c,1,10) like @user
 order by 5 desc, 1
end
  goto ENDIT 


Report12:  

begin
select a.f_srt_c   
     , a.a_uid_c+substring(b.a_uid_x,1,20) 
     , a.db_upd_t / 1000
     , 0
     , a.db_upd_d 
  from t280audit a
     , t280users b
 where a.a_uid_c = b.a_uid_c
   and a.a_uid_c = @user
 order by 5 desc, 1
end
  goto ENDIT 


Report13:  
if @user = '%' select @user = '1/1/95'

begin
select a.f_srt_c   
     , a.a_uid_c+substring(b.a_uid_x,1,20) 
     , a.db_upd_t / 1000
     , 0
     , a.db_upd_d 
  from t280audit a
     , t280users b
 where a.a_uid_c = b.a_uid_c
   and a.db_upd_d >= @user
 order by 3 desc, 1
end
  goto ENDIT 


Report14:  

begin
select a.f_srt_c   
     , a.a_uid_c+substring(b.a_uid_x,1,20) 
     , a.db_upd_t / 1000
     , 0
     , a.db_upd_d 
  from t280audit a
     , t280users b
 where a.a_uid_c = b.a_uid_c
   and substring(convert(char(8),a.db_upd_d,1),1,8) = @user
 order by 5 desc, 1
end
  goto ENDIT 


ENDIT: 

/************************************************************************
   Return to the calling environment
 ************************************************************************/

return ( 0 )
go
