drop proc p280_xxx2
go
create procedure p280_xxx2
/*
  -----------------------------------------------------------------------
      P280_XXX2 - IAS Audit Trail Information DETAIL REPORT.
  -----------------------------------------------------------------------
*/

/*
    ------  INPUT VARIABLES   ------
*/
   @actn  char(1)  = "R"  
 , @user  char(10) = "" 
 , @avg   integer  = 0   
 , @rdt   datetime = "" 
 , @cat   char(20) = "" 
as
/*
    ------ INTERNAL VARIABLES ------
*/

if @actn = 'D'  
   goto DeleteUser 


ReportUser:

begin
select '==============' , '=======  '+@user+' ========='        
     , db_upd_t / 1000, 0, db_upd_d
  from t280audit         where a_uid_c  != ' ' 
   and a_uid_c  = @user
   and db_upd_t = @avg 
   and db_upd_d = @rdt
   and f_srt_c  = @cat
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  = @user
   and db_upd_t = @avg 
   and db_upd_d = @rdt 
   and f_srt_c  = @cat
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  = @user
   and db_upd_t = @avg 
   and db_upd_d = @rdt 
   and f_srt_c  = @cat
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  = @user
   and db_upd_t = @avg 
   and db_upd_d = @rdt 
   and f_srt_c  = @cat
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  = @user
   and db_upd_t = @avg 
   and db_upd_d = @rdt 
   and f_srt_c  = @cat
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  = @user
   and db_upd_t = @avg 
   and db_upd_d = @rdt 
   and f_srt_c  = @cat
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  = @user
   and db_upd_t = @avg 
   and db_upd_d = @rdt 
   and f_srt_c  = @cat
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  = @user
   and db_upd_t = @avg 
   and db_upd_d = @rdt 
   and f_srt_c  = @cat
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  = @user
   and db_upd_t = @avg 
   and db_upd_d = @rdt 
   and f_srt_c  = @cat
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  = @user
   and db_upd_t = @avg 
   and db_upd_d = @rdt 
   and f_srt_c  = @cat
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  = @user
   and db_upd_t = @avg 
   and db_upd_d = @rdt 
   and f_srt_c  = @cat
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  = @user
   and db_upd_t = @avg 
   and db_upd_d = @rdt 
   and f_srt_c  = @cat
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  = @user
   and db_upd_t = @avg 
   and db_upd_d = @rdt 
   and f_srt_c  = @cat
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  = @user
   and db_upd_t = @avg 
   and db_upd_d = @rdt 
   and f_srt_c  = @cat
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  = @user
   and db_upd_t = @avg 
   and db_upd_d = @rdt 
   and f_srt_c  = @cat
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  = @user
   and db_upd_t = @avg 
   and db_upd_d = @rdt 
   and f_srt_c  = @cat
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  = @user
   and db_upd_t = @avg 
   and db_upd_d = @rdt 
   and f_srt_c  = @cat
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  = @user
   and db_upd_t = @avg 
   and db_upd_d = @rdt 
   and f_srt_c  = @cat
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  = @user
   and db_upd_t = @avg 
   and db_upd_d = @rdt 
   and f_srt_c  = @cat
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  = @user
   and db_upd_t = @avg 
   and db_upd_d = @rdt 
   and f_srt_c  = @cat
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  = @user
   and db_upd_t = @avg 
   and db_upd_d = @rdt 
   and f_srt_c  = @cat
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  = @user
   and db_upd_t = @avg 
   and db_upd_d = @rdt 
   and f_srt_c  = @cat
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  = @user
   and db_upd_t = @avg 
   and db_upd_d = @rdt 
   and f_srt_c  = @cat
order by 5 desc
end
  goto ENDIT 


DeleteUser:

begin
delete from t280audit
 where a_uid_c  = @user    
   and db_upd_t = @avg 
   and db_upd_d = @rdt 
   and f_srt_c  = @cat
end
  goto ReportUser


ENDIT: 

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

return ( 0 )
go
