1. concl_cv
-----------

PACKAGE concl_cv IS
       type conclass_rec is RECORD
          (EMPLOYEE_ID                       NUMBER(6),
           FIRST_NAME                        VARCHAR2(20),
           LAST_NAME                         VARCHAR2(25),
           EMAIL                             VARCHAR2(25),
           PHONE_NUMBER                      VARCHAR2(20),
           HIRE_DATE                         DATE,
           JOB_ID                            VARCHAR2(10),
           SALARY                            NUMBER(8,2),
           DEPARTMENT_ID                     NUMBER(4));
    type conclass_refcur is REF CURSOR return conclass_rec;           
END;

2. cont_cv 
----------

PACKAGE cont_cv IS
     type container_rec is RECORD
          (EMPLOYEE_ID                  NUMBER(6),
           START_DATE                   DATE,
           END_DATE                     DATE,
           JOB_ID                       VARCHAR2(10),
           DEPARTMENT_ID                NUMBER(4));
    type container_refcur is REF CURSOR return container_rec;
END;

3. cont_cv package body 
-----------------------

PACKAGE BODY cont_cv IS
  function query_container (p_department_id number) return container_refcur is 
           tempcv_container cont_cv.container_refcur;
  begin
  	open tempcv_container for select employee_id,
                                    start_date,
                                    end_date,
                                    job_id,
                                    department_id
                             from   job_history
                             where  :department_id=department_id;
  	 return tempcv_container;                         
  	  end;                                  
  END;

4. port_cv 
----------

PACKAGE port_cv IS
  type portdesc_rec is RECORD
          (DEPARTMENT_ID            NUMBER(4),
           DEPARTMENT_NAME          VARCHAR2(30));
  type portdesc_refcur is REF CURSOR return portdesc_rec;
END;


5. Q_portDescRefCurDS 
---------------------

function Q_portDescRefCurDS return port_cv.portdesc_refcur is
    temp_portdesc port_cv.portdesc_refcur;
begin
  open temp_portdesc for select department_id, department_name from departments;
  return temp_portdesc;
end;

6. Q_containerRefCurDS 
----------------------

function Q_containerRefCurDS return cont_cv.container_refcur is
    temp_container cont_cv.container_refcur;
begin
     open temp_container for select employee_id,
                                    start_date,
                                    end_date,
                                    job_id,
                                    department_id
                             from   job_history;
       return temp_container;
end;

7. Q_conclassRefCurDS
---------------------

function Q_conclassRefCurDS return concl_cv.conclass_refcur is
    temp_concl concl_cv.conclass_refcur;
begin
     open temp_concl for select EMPLOYEE_ID ,
                                FIRST_NAME,
                                LAST_NAME,
                                EMAIL ,
                                PHONE_NUMBER,
                                HIRE_DATE,
                                JOB_ID,
                                SALARY,
                                DEPARTMENT_ID
                          from  employees;
            return temp_concl;
end;
 
8. Q_CONTAINERREFCURDS
----------------------

function Q_containerRefCurDS return cont_cv.container_refcur is
    temp_container cont_cv.container_refcur;
begin
    temp_container:cont_cv.query_container (:department_id);
      return temp_container;
end;