/*  Use ls -1 > pmillet to create the file of jpg's.  Then create an
oracle table (pearl) with one field named plotno as a varchar2(11).  Bring
the pmillet file into oracle using text2ora off of prep.  Then rtrim off
the .jpg (if you make it a varchar2(7) you won't need the rtrim) and upper
the plotno field. Then create a new table from prod.s9iv with ivid and
plotno in it - 'create table pearl2 as select ivid,plotno from prod.s9iv
where plotno in (select plotno from pearl);' 

Then run the following sql's:
*/

drop table pearl3;

create table pearl3 as select b.ivid,acid,plotno from prod.iv a, pearl2 b
where a.ivid = b.ivid and plotno in (select plotno from pearl2); 

update pearl3 set plotno = lower(plotno);

commit;

drop table pearl4;

create table pearl4 as 
select acp,acno,plotno from prod.acc a, pearl3 b
where a.acid = b.acid;

commit;

insert into prepvou (acp,acno,vtype,cno,vouchered,vloc,vcontent,action)
select acp,acno,'IMAGE',76920,'04/1999',
'/npgs/images/s9/pmillet/acc/'||plotno||'.jpg', 
'Pearl Millet Panicles','A'
from pearl4;

commit;
