CREATE OR REPLACE PACKAGE PACK_SEMSADMIN_OFFEREDJOBS
AS
TYPE Generic_Cursor_Type IS REF CURSOR;
--TYPE varray_job_detail is VARRAY(100) OF STRUCT_JOB_DETAILS;
--va_varray_job_detail varray_job_detail := varray_job_detail();
va_varray_job_detail VARRAY_JOB_DETAILS := VARRAY_JOB_DETAILS();
PROCEDURE Admin_Jobs_Offered_Rtr
(
    ic_status               IN      VARCHAR2,
    or_offered_jobs         OUT     Generic_Cursor_Type,
    va_varray_job_detail    OUT     VARRAY_JOB_DETAILS     
)
/*******************************************************************************************
* Institute                 : New Jersey Institute Of Technology
* Project                   : SEMS1Admin
* Package                   : PACK_SEMSADMIN_OFFEREDJOBS
* Name                      : Admin_Jobs_Offered_Rtr.sql
* Description               : This procedure returns the list of jobs offered based on status. 
* Date                      : 
* Modification  History :
* Date          Modified by     Brief Description of Modification
* 18-Feb-2008   Prabha          Created
********************************************************************************************/
;
END PACK_SEMSADMIN_OFFEREDJOBS;
/

CREATE OR REPLACE PACKAGE BODY PACK_SEMSADMIN_OFFEREDJOBS
AS
PROCEDURE Admin_Jobs_Offered_Rtr
(
    ic_status               IN      VARCHAR2,
    or_offered_jobs         OUT     Generic_Cursor_Type,
    va_varray_job_detail    OUT     VARRAY_JOB_DETAILS     
)
/*******************************************************************************************
* Institute                 : New Jersey Institute Of Technology
* Project                   : SEMS1Admin
* Package                   : PACK_SEMSADMIN_OFFEREDJOBS
* Name                      : Admin_Jobs_Offered_Rtr.sql
* Description               : This procedure returns the list of jobs offered based on status. 
* Date                      : 
* Modification  History :
* Date          Modified by     Brief Description of Modification
* 18-Feb-2008   Prabha          Created
********************************************************************************************/
AS
    vc_query    VARCHAR2(15000)     :=  '';
    vc_query_1  VARCHAR2(15000)     :=  '';
    counter     NUMBER              := 1;
    vc_no       NUMBER              := 0;
    or_applicants_list Generic_Cursor_Type;
    
    TYPE type_appln_list IS RECORD
    (
        job_no          NUMBER(10), 
        job_title       VARCHAR2(50 BYTE),
        account_no      VARCHAR2(10 BYTE),
        head_fname      VARCHAR2(32 BYTE),
        head_minitial   VARCHAR2(10 BYTE),
        head_lname      VARCHAR2(32 BYTE),
        num             NUMBER
    );
    vn_appln_list type_appln_list;
    
    TYPE type_job_offered IS RECORD
    (
        APPL_NO         NUMBER (10),
        S_FNAME         VARCHAR2 (32),        
        S_MI            VARCHAR2 (32),
        S_LNAME         VARCHAR2 (32),
        APPL_DATE       DATE,
        DESCRIPTION     VARCHAR2 (100),
        S_UCID          VARCHAR2 (8)
    );
    vn_job_offered type_job_offered;
   
BEGIN

    vc_query := vc_query || ' SELECT jobs.job_no,job_title, account_no, head_fname, head_minitial, head_lname, num';
    vc_query := vc_query || ' FROM jobs, ( ' ;
    vc_query := vc_query || '           SELECT jobs.job_no,count(*) as num' ;
    vc_query := vc_query || '           FROM student_apps ,jobs ' ;
    vc_query := vc_query || '           WHERE jobs.job_no = student_apps.job_no' ;
    vc_query := vc_query || '           AND (student_apps.status in (''o'',''t'')) '; --|| ic_status || ')' ;
    vc_query := vc_query || '           AND jobs.status not in (''z'', ''Z'')' ;
    vc_query := vc_query || '           GROUP BY jobs.job_no' ;
    vc_query := vc_query || '           ) no_apps_off' ;
    vc_query := vc_query || ' WHERE jobs.job_no = no_apps_off.job_no' ;
    
    dbms_output.put_line('Executed Query_1');
    
    va_varray_job_detail := VARRAY_JOB_DETAILS();
    va_varray_job_detail.extend(100);
    
    OPEN or_offered_jobs FOR vc_query;
    
    LOOP
        FETCH or_offered_jobs INTO vn_appln_list;
        EXIT WHEN or_offered_jobs%NOTFOUND;
        
        vc_query_1 := '';
        
        vc_query_1 := vc_query_1 || ' SELECT stud_apps.appl_no APPL_NO, stud_apps.s_fname S_FNAME, ';
        vc_query_1 := vc_query_1 || ' stud_apps.s_mi S_MI, stud_apps.s_lname S_LNAME, '; 
        vc_query_1 := vc_query_1 || ' stud_apps.appl_date APPL_DATE, look_up.description DESCRIPTION, ' ;
        vc_query_1 := vc_query_1 || ' stud_apps.s_ucid S_UCID ' ;
        vc_query_1 := vc_query_1 || ' FROM student_apps stud_apps,jobs jbs,lookup look_up' ;
        vc_query_1 := vc_query_1 || ' WHERE stud_apps.status in (''o'',''t'') '; --(' || ic_status || ') ' ;
        vc_query_1 := vc_query_1 || ' AND jbs.job_no = stud_apps.job_no' ;
        vc_query_1 := vc_query_1 || ' AND jbs.status not in (''z '', ''Z'')' ;
        vc_query_1 := vc_query_1 || ' AND stud_apps.status = look_up.code ' ;
        vc_query_1 := vc_query_1 || ' AND look_up.type = ''st''' ;
        vc_query_1 := vc_query_1 || ' AND stud_apps.job_no = ''' || vn_appln_list.job_no || ''' ' ;
        vc_query_1 := vc_query_1 || ' ORDER BY appl_date' ;
        
        dbms_output.put_line('Executed Query_2');
        
        OPEN or_applicants_list FOR vc_query_1;
        
        LOOP
        FETCH or_applicants_list INTO vn_job_offered;
        EXIT WHEN or_applicants_list%NOTFOUND;
        
            va_varray_job_detail(counter).APPL_NO     := vn_job_offered.APPL_NO;
            va_varray_job_detail(counter).S_FNAME     := vn_job_offered.S_FNAME;
            va_varray_job_detail(counter).S_MI        := vn_job_offered.S_MI;
            va_varray_job_detail(counter).S_LNAME     := vn_job_offered.S_LNAME;
            va_varray_job_detail(counter).APPL_DATE   := vn_job_offered.APPL_DATE;
            va_varray_job_detail(counter).DESCRIPTION := vn_job_offered.DESCRIPTION;
            va_varray_job_detail(counter).S_UCID      := vn_job_offered.S_UCID;
            
            counter := counter + 1;
            
        
        END LOOP; --end of FOR
        CLOSE or_applicants_list;
    END LOOP; -- end of FETCH
    
END Admin_Jobs_Offered_Rtr;

END PACK_SEMSADMIN_OFFEREDJOBS;
/