CREATE TABLE REVENUE ( REVENUE_ID NUMBER(4,0) NOT NULL, DEPARTMENT_ID NUMBER(4,0) NOT NULL, TIMEFRAME_ID NUMBER (4,0) NOT NULL, REVENUE NUMBER(8,2) , CONSTRAINT REV_PK PRIMARY KEY (REVENUE_ID ), CONSTRAINT REV_DEPT_FK FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENTS (DEPARTMENT_ID), CONSTRAINT REV_TFR_FK FOREIGN KEY (TIMEFRAME_ID) REFERENCES TIMEFRAME (TIMEFRAME_ID) ) CREATE TABLE TIMEFRAME ( TIMEFRAME_ID NUMBER(10,0) NOT NULL , NAME VARCHAR2(50 BYTE) NOT NULL , FRAGMENTATION NUMBER(2,0) NOT NULL, STARTDATE DATE NOT NULL , ENDDATE DATE NOT NULL , CONSTRAINT TFR_PK PRIMARY KEY (TIMEFRAME_ID), CONSTRAINT TFR_UK001 UNIQUE (NAME), CONSTRAINT TFR_CK001 CHECK (FRAGMENTATION IN (1, 2, 4, 12)) ) Insert data into the timeframe table. -- INSERTING into TIMEFRAME Insert into TIMEFRAME ("TIMEFRAME_ID","NAME","FRAGMENTATION","STARTDATE","ENDDATE") values (1,'2007',1,to_date('01-01-07','DD-MM-RR'),to_date('31-12-07','DD-MM-RR')); Insert into TIMEFRAME ("TIMEFRAME_ID","NAME","FRAGMENTATION","STARTDATE","ENDDATE") values (2,'2008',1,to_date('01-01-08','DD-MM-RR'),to_date('31-12-08','DD-MM-RR')); Insert into TIMEFRAME ("TIMEFRAME_ID","NAME","FRAGMENTATION","STARTDATE","ENDDATE") values (3,'2009',1,to_date('01-01-09','DD-MM-RR'),to_date('31-12-09','DD-MM-RR')); Insert into TIMEFRAME ("TIMEFRAME_ID","NAME","FRAGMENTATION","STARTDATE","ENDDATE") values (4,'2007-I',4,to_date('01-01-07','DD-MM-RR'),to_date('31-03-07','DD-MM-RR')); Insert into TIMEFRAME ("TIMEFRAME_ID","NAME","FRAGMENTATION","STARTDATE","ENDDATE") values (5,'2007-II',4,to_date('01-04-07','DD-MM-RR'),to_date('30-06-07','DD-MM-RR')); Insert into TIMEFRAME ("TIMEFRAME_ID","NAME","FRAGMENTATION","STARTDATE","ENDDATE") values (6,'2007-III',4,to_date('01-07-07','DD-MM-RR'),to_date('30-09-07','DD-MM-RR')); Insert into TIMEFRAME ("TIMEFRAME_ID","NAME","FRAGMENTATION","STARTDATE","ENDDATE") values (7,'2007-IV',4,to_date('01-10-07','DD-MM-RR'),to_date('31-12-07','DD-MM-RR')); Insert into TIMEFRAME ("TIMEFRAME_ID","NAME","FRAGMENTATION","STARTDATE","ENDDATE") values (8,'2008-I',4,to_date('01-01-08','DD-MM-RR'),to_date('31-03-08','DD-MM-RR')); Insert into TIMEFRAME ("TIMEFRAME_ID","NAME","FRAGMENTATION","STARTDATE","ENDDATE") values (9,'2008-II',4,to_date('01-04-08','DD-MM-RR'),to_date('30-06-08','DD-MM-RR')); Insert into TIMEFRAME ("TIMEFRAME_ID","NAME","FRAGMENTATION","STARTDATE","ENDDATE") values (10,'2008-III',4,to_date('01-07-08','DD-MM-RR'),to_date('30-09-08','DD-MM-RR')); Insert into TIMEFRAME ("TIMEFRAME_ID","NAME","FRAGMENTATION","STARTDATE","ENDDATE") values (11,'2008-IV',4,to_date('01-10-08','DD-MM-RR'),to_date('31-12-08','DD-MM-RR')); Insert into TIMEFRAME ("TIMEFRAME_ID","NAME","FRAGMENTATION","STARTDATE","ENDDATE") values (12,'2009-I',4,to_date('01-01-09','DD-MM-RR'),to_date('31-03-09','DD-MM-RR')); Insert into TIMEFRAME ("TIMEFRAME_ID","NAME","FRAGMENTATION","STARTDATE","ENDDATE") values (13,'2009-II',4,to_date('01-04-09','DD-MM-RR'),to_date('30-06-09','DD-MM-RR')); Insert into TIMEFRAME ("TIMEFRAME_ID","NAME","FRAGMENTATION","STARTDATE","ENDDATE") values (14,'2009-III',4,to_date('01-07-09','DD-MM-RR'),to_date('30-09-09','DD-MM-RR')); Insert into TIMEFRAME ("TIMEFRAME_ID","NAME","FRAGMENTATION","STARTDATE","ENDDATE") values (15,'2009-IV',4,to_date('01-10-09','DD-MM-RR'),to_date('31-12-09','DD-MM-RR')); commit; -- INSERTING into REVENUE Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (1,60,4,1000); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (2,60,5,2000); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (3,60,6,1000); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (4,60,7,3000); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (5,60,8,1500); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (6,60,9,1500); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (7,60,10,2500); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (8,60,11,1000); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (9,60,12,900); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (10,60,13,1100); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (11,60,14,1500); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (12,60,15,3000); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (13,70,4,1000); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (14,70,5,2000); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (15,70,6,1000); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (16,70,7,1000); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (17,70,8,2500); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (18,70,9,2500); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (19,70,10,3500); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (20,70,11,3000); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (21,70,12,1900); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (22,70,13,2100); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (23,70,14,3500); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (24,70,15,4000); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (25,80,4,100); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (26,80,5,200); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (27,80,6,100); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (28,80,7,100); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (29,80,8,250); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (30,80,9,250); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (31,80,10,350); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (32,80,11,300); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (33,80,12,190); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (34,80,13,210); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (35,80,14,350); Insert into REVENUE ("REVENUE_ID","DEPARTMENT_ID","TIMEFRAME_ID","REVENUE") values (36,80,15,400); commit; create or replace type revenue_tab as table of revenue_t; create or replace type revenue_t as object ( deptartment_id varchar2(4) , deptartment_name varchar2(100) , rev_per_period_tab revenue_per_period_tab ); create or replace type revenue_per_period_t as object ( period varchar(8) , revenue number(10,2) ); create or replace type revenue_per_period_tab as table of revenue_per_period_t; create or replace PACKAGE HR_REVENUE_PKG AS /* *functie voor het ophalen van de faseringsperiode voor *bepaalde begrotingspost. Deze functie kan op 2 manieren gebruikt worden * 1. Voor het ophalen van een op jaarbasis gesommerd overzocht van alle bestaande faseringen * 2. Voor het ophalen van een overzocht van alle bestaande faseringen tussen 2 periodes */ function get_period( p_department_id in number default null ,p_year in number default null ) return revenue_per_period_tab; /* *Functie welke wordt gebruikt door het faseringenscherm *Om de faseringen voor een bepaalde begroting op te halen */ function get_revenue ( p_department_id in number default null ,p_year in number default null ) return revenue_tab; /* *Functie voor het opslaan van faseringen. Bepaald welke faseringen gewijzigd en/of nieuw zijn *en voegt deze toe aan de begrotingspostfaseringen. */ END HR_REVENUE_PKG; create or replace PACKAGE BODY HR_REVENUE_PKG AS --create periode type type l_revenue_per_period_t is record ( period varchar(7) , revenue number(10,2) ); TYPE l_revenue_per_period_tab IS TABLE OF l_revenue_per_period_t INDEX BY binary_integer; function get_period( p_department_id in number default null ,p_year in number default null ) return revenue_per_period_tab is t_periode revenue_per_period_tab; begin if (p_year is null) then dbms_output.put_line('Startjaar en eindjaar leeg'); select cast(collect(revenue_per_period_t( period , revenue )) as revenue_per_period_tab) into t_periode from ( select * from table ( select cast(collect(revenue_per_period_t( startyear , sum(rev.revenue))) as revenue_per_period_tab) FROM ( SELECT rev.revenue_ID , rev.department_id , tfr.timeframe_ID timeframe_id , tfr.startdate , EXTRACT (YEAR FROM tfr.startdate) startyear , EXTRACT (YEAR FROM tfr.enddate) stopyear FROM revenue rev , departments dep , timeframe tfr WHERE dep.department_ID = rev.department_ID(+) AND rev.timeframe_ID = tfr.timeframe_ID order by tfr.name ) dpt , revenue rev WHERE rev.department_id(+) = dpt.department_id AND rev.timeframe_id(+) = dpt.timeframe_id and startyear >= nvl(p_year,startyear) and startyear <= nvl(p_year,startyear) and rev.department_id = nvl(p_department_id,rev.department_id) group by startyear)) order by 1 ; else dbms_output.put_line('Startjaar en eindjaar niet leeg'); select cast(collect(revenue_per_period_t(dpt.timeframe_name , rev.revenue )) as revenue_per_period_tab) into t_periode FROM (SELECT rev.revenue_id , rev.department_id , tfr.timeframe_id timeframe_id , tfr.name timeframe_name , tfr.startdate , EXTRACT (YEAR FROM tfr.startdate) startyear , EXTRACT (YEAR FROM tfr.enddate) stopyear FROM revenue rev , departments dep , timeframe tfr WHERE rev.department_ID(+) = dep.department_id AND tfr.timeframe_id = rev.timeframe_id order by 6 asc )dpt , revenue rev WHERE rev.department_id(+) = dpt.department_id AND rev.timeframe_id(+) = dpt.timeframe_id and startyear >= nvl(p_year,startyear) and startyear <= nvl(p_year,startyear) and rev.department_id = nvl(p_department_id,rev.department_id) order by timeframe_name ; end if; return t_periode; end; function get_revenue ( p_department_id in number default null ,p_year in number default null ) return revenue_tab /* *Functie welke wordt gebruikt door het faseringenscherm *Om de faseringen voor een bepaalde begroting op te halen */ is l_t_revenue revenue_tab; begin -- --Cast result in pgi_faseringen_tab type -- select cast(collect(revenue_t( department_id ,department_name ,get_period( department_id ,p_year ) ))as revenue_tab) into l_t_revenue from( select department_id , department_name from departments department where (nvl(p_department_id , department.department_ID) = department.department_ID) and exists (select 1 from revenue rev where rev.department_id = department.department_id) ); --dbms_output.put_line('aantal '||l_t_revenue.count); --declare --l_t_revenue revenue_tab; --begin --select hr_revenue_pkg.get_revenue(70, 2008) into l_t_revenue from dual; --dbms_output.put_line('aantal '||l_t_revenue.count); --dbms_output.put_line('aantal '||l_t_revenue(1).deptartment_name); --dbms_output.put_line('aantal '||l_t_revenue(1).deptartment_id); --dbms_output.put_line('aantal '||l_t_revenue(1).rev_per_period_tab(1).period); --dbms_output.put_line('aantal '||l_t_revenue(1).rev_per_period_tab(1).revenue); --dbms_output.put_line('aantal '||l_t_revenue(1).rev_per_period_tab(2).period); --dbms_output.put_line('aantal '||l_t_revenue(1).rev_per_period_tab(2).revenue); --dbms_output.put_line('aantal '||l_t_revenue(1).rev_per_period_tab(3).period); --dbms_output.put_line('aantal '||l_t_revenue(1).rev_per_period_tab(3).revenue); --dbms_output.put_line('aantal '||l_t_revenue(1).rev_per_period_tab(4).period); --dbms_output.put_line('aantal '||l_t_revenue(1).rev_per_period_tab(4).revenue); --dbms_output.put_line('aantal '||l_t_revenue(1).rev_per_period_tab(5).period); --dbms_output.put_line('aantal '||l_t_revenue(1).rev_per_period_tab(5).revenue); --dbms_output.put_line('aantal '||l_t_revenue(1).rev_per_period_tab(6).period); --dbms_output.put_line('aantal '||l_t_revenue(1).rev_per_period_tab(6).revenue); --dbms_output.put_line('aantal '||l_t_revenue(1).rev_per_period_tab(7).period); --dbms_output.put_line('aantal '||l_t_revenue(1).rev_per_period_tab(7).revenue); --end; return l_t_revenue; end; END HR_REVENUE_PKG;