{tipteadb.ace - ALLANS - 6/21/98 
 Produces teacher database import file for TIP.
 command line is  sacego tipteadb '"'} 

database finance end 

define
  variable bld int
  variable address1 char(10)
  variable address2 char(10)
  variable phoneno  char(12)
  param [1] qt char(1)
end

input
  prompt for bld using "BUILDING NUMBER:  "
end
 
output 
  page  length  9999
  left  margin  0 
  right margin  0 
  top   margin  0 
  report to pipe "cat > $SMSRPTDIR/tip/2001/teadata.unl" 
end 

select e.empl_no, l_name, f_name, addr1, addr2, 
  city, state, zip, birthdate, p.home_phone 
from employee e, person p
where e.empl_no = p.empl_no and
       base_loc = $bld and
       p.status = '0001' and
    p.term_date is null and
      e.empl_no is not null and
         l_name is not null
end 
format
  on every row 
	if addr1[1,1]='*'
	then begin let address1 = "XXXXXXXXXX"
	     	   let address2 = "XXXXXXXXXX"
		   let phoneno  = "999-999-9999"
	end
	else begin let address1 = addr1
	     	   let address2 = addr2
		   let phoneno  = home_phone
	end
  print
    qt, empl_no using "#########",qt,",",
    qt,l_name clipped,", ",f_name clipped,qt,",",
    qt,address1 clipped," ",address2 clipped,qt,",",
    qt,city clipped,qt,",",
    qt,state ,qt,",",
    qt,zip clipped,qt,",",
    qt,phoneno clipped,qt
end