set echo off
Rem
Rem Script Name		: dbc_audit_all_rpt.sql
Rem Authors Name	: DAB
Rem Purpose		: Create the database audit report.
Rem
Rem Modification History:
Rem Date	By		Reason
Rem 11/21/95	Daniel Booth	Initial Creation.
Rem 10/16/96    Dawn Wisniewski Add selection dates to header
Rem 12/17/96    Dawn Wisniewski Changed dbc_date select to be a between and
Rem				changed params to be to_date instead of
Rem				to_char on dbc_date
Rem 10/24/97    Stephen Rea     Generalized for CES to a single report
Rem 1/25/00     Stephen Rea     Y2K Fixes
Rem

Rem   Note: This script uses a concatenated string of fields in order to
Rem   only output one line for inserted/deleted fields or two lines for
Rem   changed fields (otherwise, you would get an extra blank line for
Rem   inserted/deleted fields in place of the After value).  This also
Rem   allows us to print the entire "changed by" information on the first
Rem   field's lines (using "break on" with nodup may leave out some of
Rem   those "changed by" fields if they were the same as the previously
Rem   changed record). 

set showmode off verify off feedback off pause off timing off
set linesize 105
set pagesize 45

set termout off
set heading off
set recsep off

column from_dt_set new_value from_dt
column to_dt_set new_value to_dt
select to_char(decode(sign(8-length('&1')),-1,
  to_date('&1','mm/dd/yyyy'),to_date('&1','mm/dd/rr')),
  'mm/dd/yyyy') from_dt_set from dual;
select to_char(decode(sign(8-length('&2')),-1,
  to_date('&2','mm/dd/yyyy'),to_date('&2','mm/dd/rr')),
  'mm/dd/yyyy') to_dt_set from dual;

column d new_value db_name
column m new_value machine
column u new_value today
column v new_value ttime

select name d from v$database;
select to_char(sysdate,'mm/dd/yyyy') u, to_char(sysdate,'fmhh:fmmi:ss AM') v
  from dual;
select rtrim(machine) m from v$session where machine is not null
  and rownum = 1;

ttitle -
left   'Database Change Audit Report' -
center 'Database: ' db_name '; Machine: ' machine -
right  'Detail: full   ' today skip 1 -
left   '  &from_dt - &to_dt' -
center 'Fields Set: &3' -
right  ttime skip 2 -
left   'Changed By Record ID    Table_Field Name               Before' skip 1 - 
left   '---------- ------------ ------------------------------ --------------------------------------------------' skip 1 -
left   'Change Date/Time  Spriden ID Spriden Name              After' skip 1 - 
left   '----------------- ---------- ------------------------- --------------------------------------------------' skip 1

btitle -
skip 1 -
center 'Page' format 999 sql.pno

spool /home/common/dbc_audit_rpt.lst
select decode(dbc_field_seq,1,rpad(' ',105) || rpad(dbc_user_name,10) || ' ' ||
  dbc_record_id || ' ' || rpad(system.dbc_changes.dbc_field_name,30) || ' ' ||
  rpad(decode(dbc_chg_ins_del,'I',dbc_after,dbc_before),50) ||
  to_char(dbc_chg_date,'mm/dd/yy hh:mi:ss') || ' ' || spriden_id || '  ' ||
  rpad(substr(rtrim(spriden_last_name) || ', ' || rtrim(spriden_first_name) ||
  ' ' || rtrim(spriden_mi),1,25),25) || ' ' ||
  decode(dbc_chg_ins_del,'C',dbc_after, 'I','<- Record Inserted',
  '<- Record Deleted'),
  rpad(' ',24) || rpad(system.dbc_changes.dbc_field_name,30) || ' ' ||
  rpad(decode(dbc_chg_ins_del,'I',dbc_after,dbc_before),50) ||
  decode(dbc_chg_ins_del,'C',rpad(' ',55) || dbc_after,''))
  from system.dbc_changes, system.dbc_columns, saturn.spriden
where trunc(dbc_chg_date) between to_date('&from_dt','mm/dd/yyyy') and
  to_date('&to_dt','mm/dd/yyyy')
  and spriden_pidm = dbc_key
  and spriden_change_ind is null
  and system.dbc_changes.dbc_field_name = system.dbc_columns.dbc_field_name
  and (dbc_field_seq = 1 or instr(dbc_report_set,'&3') > 0)
order by dbc_chg_date, dbc_record_id, dbc_field_seq;
spool off
exit