set showmode off echo off
set heading off pagesize 0 timing off feedback off linesize 240
rem
rem Script: fixwho.sql
rem Purpose: Show who entered the given document.
rem
rem Author: Stephen Rea
rem Released: 5/26/99
rem
accept docnum char prompt 'Enter document number to find: '
set termout off verify off
spool fd_do.sql
select 'define docnum = ' || upper('&docnum') from dual;
spool off
@fd_do.sql
spool fd_do.sql
select '!echo Document &docnum not found in fobappd' || chr(10) || '/*'
   from dual
   where not exists (select 'x' from fobappd where fobappd_doc_num = '&docnum');
spool off
@fd_do.sql
set termout on
select 'User ' || userid || ' (' || first || ' ' || last ||
   ') entered document &docnum'
   from
      (select distinct fabinvh_user_id userid,spriden_first_name first,
         spriden_last_name last
         from fabinvh,spriden
         where substr('&docnum',1,1) = 'I'
         and fabinvh_code = '&docnum'
         and fabinvh_user_id = substr(upper(spriden_first_name),1,1) ||
            substr(upper(spriden_last_name),1,length(substr(fabinvh_user_id,2)))
         and spriden_change_ind is null
   union
      select distinct fabinvh_user_id,'?','?'
         from fabinvh
         where substr('&docnum',1,1) = 'I'
         and fabinvh_code = '&docnum'
         and fabinvh_user_id not in
            (select substr(upper(spriden_first_name),1,1) ||
               substr(upper(spriden_last_name),1,
               length(substr(fabinvh_user_id,2))) from spriden)
   union
      select distinct fgbench_user_id,spriden_first_name,spriden_last_name
         from fgbench,spriden
         where substr('&docnum',1,1) = 'E'
         and fgbench_num = '&docnum'
         and fgbench_user_id = substr(upper(spriden_first_name),1,1) ||
            substr(upper(spriden_last_name),1,length(substr(fgbench_user_id,2)))
         and spriden_change_ind is null
   union
      select distinct fgbench_user_id,'?','?'
         from fgbench
         where substr('&docnum',1,1) = 'E'
         and fgbench_num = '&docnum'
         and fgbench_user_id not in
            (select substr(upper(spriden_first_name),1,1) ||
               substr(upper(spriden_last_name),1,
               length(substr(fgbench_user_id,2))) from spriden)
   union
      select distinct fgbjvch_user_id,spriden_first_name,spriden_last_name
         from fgbjvch,spriden
         where substr('&docnum',1,1) in ('J','F')
         and fgbjvch_doc_num = '&docnum'
         and fgbjvch_user_id = substr(upper(spriden_first_name),1,1) ||
            substr(upper(spriden_last_name),1,length(substr(fgbjvch_user_id,2)))
         and spriden_change_ind is null
   union
      select distinct fgbjvch_user_id,'?','?'
         from fgbjvch
         where substr('&docnum',1,1) in ('J','F')
         and fgbjvch_doc_num = '&docnum'
         and fgbjvch_user_id not in
            (select substr(upper(spriden_first_name),1,1) ||
               substr(upper(spriden_last_name),1,
               length(substr(fgbjvch_user_id,2))) from spriden));
set termout off
*/
set pagesize 14
set heading on termout on
set timing on feedback 6 verify on echo on showmode both