drop table davids_first_temp_table;
prompt "obtain vehicle information for all 'boom equipped' units"
create table davids_first_temp_table as
(select a.eq_equip_no, a.loc_assign_repr_loc, a.loc_assign_pm_loc,
a.loc_station_loc, a.loc_stored_loc, a.procst_proc_status,
a.eqtyp_equip_type
from eq_main@fatest a
where a.eqtyp_equip_type in
(select b.eqtyp_equip_type
from eq_subsys_detail@fatest b
where b.subprop_subsys_prop = 'BOOM MAKE'
and b.description is not null));

commit;
prompt "add additional columns to result table from above"
alter table davids_first_temp_table
add (unit_spec_sched number(3),
class_spec_sched number(3),
last_y_z_job varchar2(8));
commit;



prompt "locate unit-spec dielectric schedule"
update davids_first_temp_table a
set unit_spec_sched = (select b.description
from eq_subsys_detail@fatest b
where a.eqtyp_equip_type = b.eqtyp_equip_type
and b.subprop_subsys_prop = 'DIELECTRIC SCHED');

prompt "locate class-spec dielectric schedule"
update davids_first_temp_table a
set class_spec_sched = (select b.description
from eq_subsys_detail@fatest b
where a.eqtyp_equip_type = b.eqtyp_equip_type
and b.subprop_subsys_prop = 'DIELECTRIC SCHED');


prompt "locate last 'Y', 'Z' task and obtain date"
select a.eq_equip_no, decode(a.reas_reas_for_repair,'Y','1','Z','1','0'),
max(datetime_in_service)
from tsk_main@fatest a, job_main@fatest b
where decode(a.reas_reas_for_repair,'Y','1','Z','1','0') = '1'
and a.loc_work_order_loc = b.loc_work_order_loc
and a.work_order_yr = b.work_order_yr
and a.work_order_no = b.work_order_no
group by a.eq_equip_no,
decode(a.reas_reas_for_repair,'Y','1','Z','1','0');
commit;


prompt "update Y & Z data to dielectric schedule"