drop table temp_ed_prod_completed;

create table temp_ed_prod_completed
(
ed_prod_id	char(20) not null  CONSTRAINT temppk_ed_prod_completed  PRIMARY KEY,
proposed_reg_id	char(20) not null,
actual_reg_id	char(20) null,
status		varchar2(10) not null,
ct		integer
);


drop table temp_reg;

create table temp_reg(
reg_id		char(20) 	not null,
item_type	int		not null,
status		int		not null,
student_id	char(20)	not null,
product_id	char(20)	not null
);

create index tempi_reg_sid_pid on temp_reg (student_id, product_id);

insert into temp_reg (reg_id, item_type, status, student_id, product_id)
select 
	t.id, oi.item_typ, oi.status, t.student_id, t.product_id
from
	tpt_transcript t , tpt_oe_order_items oi 
where
	t.order_item_id = oi.id and
		(
			(oi.item_typ = 7 and oi.status in (300, 400, 500)) or
			(oi.item_typ = 1 and oi.status in (100, 103, 300, 400, 500))
		)
union
select 
	r.id, oi.item_typ, oi.status, r.student_id, oi.part_id
from 	
	tpt_registration r ,tpt_oe_order_items oi 
where
	r.id = oi.reg_id and 
	r.student_id is not null and
	oi.status in (300, 400, 500);

commit;