INSERT INTO OT
(TP,MON,SPI) 1,'JAN',select SP_ID from sp
where 
sp.SP_ID in(select ds.FK_SP_ID from DET_SP ds
where sp.SP_ID=ds.FK_SP_ID) 

select SP_ID  from sp where sp.SP_ID in  
                  (select ds.FK_SP_ID from DET_SP ds     
                    where    sp.SP_ID=ds.FK_SP_ID);

insert into bs
	(
	sec,tr_dt,rv,sp
	) 
	select
	'Tested' as sec,
	v_dt tr_dt,
	sp_id FROM Sp 
   WHERE sp_id IN (SELECT fksp_id FROM CUST 
	WHERE fksp_id=sp_id),
	count(1) rv
	from cust c where sub_type = 1

select sp_id FROM Sp 
   WHERE sp_id IN (SELECT fksp_id FROM CUST 
	WHERE fksp_id=sp_id);

...
(SELECT fksp_id FROM CUST 
 WHERE fksp_id=sp_id),
 count(1) rv                    --> this two lines are especially
 from cust c where sub_type = 1 --> interesting

SQL> declare
    v_osp number(5);
    v_mtsp number(5);
   begin
    select sp_id into v_osp from   sp
   where sp_id in(select fk_spi from ot
    where fk_spi=sp_id);
   select sp_id into v_mtsp from   sp
    where sp_id in(select fk_msp from mt
   where fk_msp=sp_id);
   INSERT INTO det_sp
   (temp_sp,name,act_rec)
   (v_mtsp, 'JAN','Y');
 INSERT INTO det_sp
   (temp_sp,name,act_rec)
   ( v_osp,'JAN','Y' );
   commit;
   end;
   /
(v_mtsp, 'JAN','Y');
 *

temp_sp,	name	act_rec
101	Curtest	Y
102	Curtest	Y
103	Curtest	Y
108	Curtest	Y
109	Curtest	Y

DECLARE 
v_Cot NUMBER(6); 
CURSOR Cot IS select sp_id  from   sp
where sp_id in(select fk_spi from ot
where fk_spi=sp_id); 
BEGIN 
OPEN cot; 
FETCH Cot INTO v_Cot; 
INSERT INTO det_sp (temp_sp,name,act_rec) values(v_cot, 'curtest','z');
EXIT WHEN Cot%NOTFOUND; 
END LOOP; 
COMMIT; 
CLOSE Cot; 
end;
/