CREATE OR REPLACE PROCEDURE test (
test_cursor IN OUT reports.rep_type,
date_parameter IN system_days.daytime%TYPE)
is VRPPS_TEMP VRPPS_TAB;
begin
SELECT CAST(MULTISET(select distinct CODE,CLASS,
shutin.status(production_day,object_id,status) STATUS
FROM test1 where production_day=date_parameter) as VRPPS_TAB)
into VRPPS_TEMP
from dual;
open test_cursor for
select 1 id,null status,count(code) count,'on' descr from table(cast(VRPPS_TEMP as VRPPS_TAB)) where status='A' and class='P'
union
select 2,'out of',count(code),'available' from table(cast(VRPPS_TEMP as VRPPS_TAB)) where status in('A','S') and class='P'
union
select 3,null,count(distinct code),'total' from status where class='P' and prod_date= date_parameter;
VRPPS_TEMP.delete;
dbms_session.free_unused_user_memory;
end;
/
|