PROCEDURE assess(STUD_ID VARCHAR2, STUD_YEAR NUMBER, STUD_TYPE CHAR) IS CURSOR subj_cur IS SELECT e.student_id, s.subject_id, s.subj_code, s.subject_title, s.units, s.pay_units, d.department_name, s.tuition_fee_type, s.lab_fee_type, f.year, f.amount FROM regist.enrol e, regist.offering o, regist.subject s, fee_details f, regist.student st, regist.department d WHERE (e.student_id = STUD_ID) AND (e.offering_id = o.offering_id) AND (o.subject_id = s.subject_id) AND (f.fee_no = s.tuition_fee_type) AND (st.student_id=e.student_id) AND ((f.year = STUD_YEAR) OR (f.year=0)) AND (s.department_id = d.department_id); subj_rec subj_cur%ROWTYPE; fee_rec fee_details%ROWTYPE; lab_rec fee_details%ROWTYPE; a_rec assessment%ROWTYPE; lab_amount fee_details.amount%TYPE; TF1 number(10,2); -- Regular Subjects TF2 number(10,2); -- Accounting and Blaw TF3 number(10,2); -- CS, Engg, EDP LF9 number(10,2); -- Psychology Lab Fee LF10 number(10,2); -- Biology/Chemistry LF11 number(10,2); -- Advance Chemistry LF12 number(10,2); -- Advance Chemistry LF13 number(10,2); -- Zoology LF14 number(10,2); -- Physics LF15 number(10,2); -- DEVCOM Lab LF16 number(10,2); -- Speech/Call Center LF17 number(10,2); -- Ellectronics and Communications Lab LF18 number(10,2); -- Ellectronics and Communications Lab LF19 number(10,2); -- Ellectronics and Communications Lab LF20 number(10,2); -- Engg Drawing 1 LF21 number(10,2); -- Engg Drawing 1 LF22 number(10,2); -- Engg Drawing 2 LF23 number(10,2); -- Engg Drawing 2 LF24 number(10,2); -- EDP LF25 number(10,2); -- Psychology Computer Lab LF26 number(10,2); -- Information Technology LF27 number(10,2); -- Engg Elective LF28 number(10,2); -- Engg Elective LF29 number(10,2); -- Engg Elective LF30 number(10,2); -- One CS Sub with lab LF31 number(10,2); -- Two CS Sub with lab LF32 number(10,2); -- Three CS Sub with lab tuition number(10,2); lab number(10,2); other_fee number(10,2); other_assessment number(10,2); total number(10,2); bal number(10,2); option1 number(10,2); option2 number(10,2); option3 number(10,2); option4 number(10,2); rowcount number(3); subj_count number(3); SRA number(1); -- 1 with SRA; 0, otherwise NSTP number(1); -- 1 with NSTP; 0, otherwise SEMESTER number(1); -- Values are 1, 2 and 3 sdate varchar2(20); stime date; BEGIN tuition := 0; lab := 0; rowcount := 0; SRA := 0; TF1 := 0; TF3 := 0; other_fee := 0; other_assessment := 0; NSTP := 0; TF2 := 0; subj_count := 0; LF9 := 0; LF10 := 0; LF11 := 0; LF12 := 0; LF13 := 0; LF14 := 0; LF15 := 0; LF16 := 0; LF17 := 0; LF18 := 0; LF19 := 0; LF20 := 0; LF21 := 0; LF22 := 0; LF23 := 0; LF24 := 0; LF25 := 0; LF26 := 0; LF27 := 0; LF28 := 0; LF29 := 0; LF30 := 0; LF31 := 0; LF32 := 0; -- Get date and time SELECT to_char(sysdate,'DD-MON-YY') INTO sdate FROM DUAL; SELECT to_date(to_char(sysdate,'HH24:MI:SS'),'HH24:MI:SS') INTO stime FROM DUAL; --check how many rows are retrieved SELECT count(*) into rowcount FROM enrol e, offering o, subject s, fee_details f, student st WHERE (e.student_id = STUD_ID) AND (e.offering_id = o.offering_id) AND (o.subject_id = s.subject_id) AND (f.fee_no = s.tuition_fee_type) AND (st.student_id=e.student_id) AND ((f.year = STUD_YEAR) OR (f.year=0)); OPEN subj_cur; -- Compute Total Tuition Fee LOOP FETCH subj_cur into subj_rec; EXIT WHEN subj_cur%NOTFOUND; dbms_output.put_line(subj_rec.subj_code || ' Tuition Fee: ' || subj_rec.amount || ' x ' || subj_rec.pay_units || ' units = ' || (subj_rec.amount * subj_rec.pay_units)); -- If Regular Subject IF subj_rec.tuition_fee_type = 'TF007' THEN TF1 := TF1 + (subj_rec.pay_units * subj_rec.amount); END IF; -- If Accounting and BLAW Subject IF subj_rec.tuition_fee_type = 'TF008' THEN TF2 := TF2 + (subj_rec.pay_units * subj_rec.amount); END IF; -- If EDP, CS and Engg Subject IF subj_rec.tuition_fee_type = 'TF009' THEN TF3 := TF3 + (subj_rec.pay_units * subj_rec.amount); END IF; -- Compute Laboratory Fee IF subj_rec.lab_fee_type IS NOT NULL THEN -- Determine if subject belongs to computer science IF subj_rec.department_name = 'Computer Science' THEN subj_count := subj_count + 1; END IF; -- Determine Lab Amount select amount into lab_amount from fee_details where fee_no = subj_rec.lab_fee_type; IF (subj_rec.lab_fee_type = 'LF009') THEN LF9 := LF9 + lab_amount; ELSIF (subj_rec.lab_fee_type = 'LF010') THEN LF10 := LF10 + lab_amount; ELSIF (subj_rec.lab_fee_type = 'LF011') THEN LF11 := LF11 + lab_amount; ELSIF (subj_rec.lab_fee_type = 'LF012') THEN LF12 := LF12 + lab_amount; ELSIF (subj_rec.lab_fee_type = 'LF013') THEN LF13 := LF13 + lab_amount; ELSIF (subj_rec.lab_fee_type = 'LF014') THEN LF14 := LF14 + lab_amount; ELSIF (subj_rec.lab_fee_type = 'LF015') THEN LF15 := LF15 + lab_amount; ELSIF (subj_rec.lab_fee_type = 'LF016') THEN LF16 := LF16 + lab_amount; ELSIF (subj_rec.lab_fee_type = 'LF017') THEN LF17 := LF17 + lab_amount; ELSIF (subj_rec.lab_fee_type = 'LF018') THEN LF18 := LF18 + lab_amount; ELSIF (subj_rec.lab_fee_type = 'LF019') THEN LF19 := LF19 + lab_amount; ELSIF (subj_rec.lab_fee_type = 'LF020') THEN LF20 := LF20 + lab_amount; ELSIF (subj_rec.lab_fee_type = 'LF021') THEN LF21 := LF21 + lab_amount; ELSIF (subj_rec.lab_fee_type = 'LF022') THEN LF22 := LF22 + lab_amount; ELSIF (subj_rec.lab_fee_type = 'LF023') THEN LF23 := LF23 + lab_amount; ELSIF (subj_rec.lab_fee_type = 'LF024') THEN LF24 := LF24 + lab_amount; ELSIF (subj_rec.lab_fee_type = 'LF025') THEN LF25 := LF25 + lab_amount; ELSIF (subj_rec.lab_fee_type = 'LF026') THEN LF26 := LF26 + lab_amount; ELSIF (subj_rec.lab_fee_type = 'LF027') THEN LF27 := LF27 + lab_amount; ELSIF (subj_rec.lab_fee_type = 'LF028') THEN LF28 := LF28 + lab_amount; ELSIF (subj_rec.lab_fee_type = 'LF029') THEN LF29 := LF29 + lab_amount; ELSIF (subj_rec.lab_fee_type = 'LF030') THEN LF30 := LF30 + lab_amount; ELSIF (subj_rec.lab_fee_type = 'LF031') THEN LF31 := LF31 + lab_amount; ELSIF (subj_rec.lab_fee_type = 'LF032') THEN LF32 := LF32 + lab_amount; ELSE NULL; END IF; dbms_output.put_line(subj_rec.subj_code ||' Lab Fee: ' || lab_amount); END IF; IF substr(subj_rec.subj_code,1,4) = 'UPPR' THEN SRA := 1; dbms_output.put_line('*** SRA Found ***'); END IF; IF substr(subj_rec.subj_code,1,4) = 'NSTP' THEN NSTP := 1; END IF; -- Check for the current semester SELECT semester INTO SEMESTER FROM offering where subject_id = subj_rec.subject_id; END LOOP; dbms_output.put_line('--------------------------------'); -- Check CS Subjects with Lab for discount IF subj_count = 2 THEN SELECT amount INTO LF31 FROM fee_details WHERE fee_no = 'LF031'; LF26 := 0; END IF; IF subj_count >= 3 THEN SELECT amount INTO LF32 FROM fee_details WHERE fee_no = 'LF032'; LF26 := 0; END IF; -- Compute Total Lab Fee lab := LF9 + LF10 + LF11 + LF12 + LF13 + LF14 + LF15 + LF16 + LF17 + LF18 + LF19 + LF20 + LF21 + LF22; lab := lab + LF23 + LF24 + LF25 + LF26 + LF27 + LF28 + LF29 + LF30 + LF31 + LF32; -- Compute Total Tuition Fee tuition := TF1 + TF2 + TF3; --Compute Total Other Fee FOR OF_rec IN (select fee_no, fee_name, acct_code, amount from fee_details where (educ_level=2) AND (fee_no like 'OF%') AND ((year=stud_year) OR (year=0))) LOOP other_fee := other_fee + OF_rec.amount; dbms_output.put_line(OF_rec.fee_no || ' | ' || OF_rec.fee_name || ' | ' || OF_rec.amount); -- Save Other Fee Detail INSERT INTO assessment_details values(STUD_ID,OF_rec.fee_no,OF_rec.acct_code,sdate,stime,OF_rec.amount,OF_rec.amount); END LOOP; dbms_output.put_line('--------------------------------'); --Compute Total Other Assessment FOR OA_rec IN (select fee_no, fee_name, acct_code,amount from fee_details where (educ_level=2) AND (fee_no like 'OA%') AND ((year=stud_year) OR (year=0))) LOOP IF ((substr(OA_rec.fee_name,1,4) = 'NSTP') AND (NSTP=0)) THEN dbms_output.put_line('*** NSTP IGNORED ***'); ELSIF ((substr(OA_rec.fee_name,1,3) = 'SRA') AND (SRA=0)) THEN dbms_output.put_line('*** SRA IGNORED ***'); ELSIF ((substr(OA_rec.fee_name,1,5) = 'Group') AND ((SEMESTER=2) OR (SEMESTER=3)) AND (STUD_TYPE = 'O')) THEN dbms_output.put_line('*** GROUP INSURANCE IGNORED ***'); ELSIF ((substr(OA_rec.fee_name,1,6) = 'Energy') AND ((SEMESTER=2) OR (SEMESTER=3)) AND (STUD_TYPE = 'O')) THEN dbms_output.put_line('*** ENERGY FEE IGNORED ***'); ELSIF ((substr(OA_rec.fee_name,1,3) = 'PTA') AND (SEMESTER=3)) THEN dbms_output.put_line('*** PTA IGNORED ***'); ELSIF ((substr(OA_rec.fee_name,1,6) = 'PRISAA') AND (SEMESTER=3)) THEN dbms_output.put_line('*** PTA IGNORED ***'); ELSIF ((substr(OA_rec.fee_name,1,9) = 'Formation') AND (SEMESTER=3)) THEN dbms_output.put_line('*** PTA IGNORED ***'); ELSE other_assessment := other_assessment + OA_rec.amount; dbms_output.put_line(OA_rec.fee_no || ' | ' || OA_rec.fee_name || ' | ' || OA_rec.amount); -- Save Other Assessment Detail INSERT INTO assessment_details values(STUD_ID,OA_rec.fee_no,OA_rec.acct_code,sdate,stime,OA_rec.amount,OA_rec.amount); END IF; END LOOP; dbms_output.put_line('--------------------------------'); -- Compute Total Assessment total := tuition + lab + other_fee + other_assessment; -- Compute Option 1 option1 := ROUND((0.95*tuition) + (0.95*other_fee) + other_assessment + (0.95*lab),0); -- Compute Option 2 option2 := ROUND((tuition * 0.20) + other_fee + other_assessment + (lab * 0.20),0); -- Compute Option 3 option3 := ROUND((tuition*0.2) + (other_fee*0.85) + other_assessment + (lab*0.2),0); -- Compute Option 4 option4 := ROUND(total/5,0); dbms_output.put_line('--------------------------------'); dbms_output.put_line('Total Tuition Fee: ' || tuition); dbms_output.put_line('Total Laboratory Fee: ' || lab); dbms_output.put_line('Total Other Fee: ' || other_fee); dbms_output.put_line('Total Other Assessment: ' || other_assessment); dbms_output.put_line('--------------------------------'); dbms_output.put_line('Total Payable: ' || total); dbms_output.put_line('Option 1: ' || option1); dbms_output.put_line('Option 2: ' || option2); dbms_output.put_line('Option 3: ' || option3); dbms_output.put_line('Option 4: ' || option4); dbms_output.put_line('--------------------------------'); -- Save Tuition Fee Details for Regular Subjects SELECT * INTO fee_rec from fee_details where fee_name like 'Regular%' AND fee_no like 'TF%'; IF TF1 > 0 THEN INSERT INTO assessment_details values(STUD_ID,fee_rec.fee_no,fee_rec.acct_code,sdate,stime,TF1,TF1); commit; END IF; -- Save Tuition Fee Details for Accounting and BLAW SELECT * INTO fee_rec from fee_details where fee_name like 'Accounting%' AND fee_no like 'TF%'; IF TF2 > 0 THEN INSERT INTO assessment_details values(STUD_ID,fee_rec.fee_no,fee_rec.acct_code,sdate,stime,TF2,TF2); commit; END IF; -- Save Tuition Fee Details for EDP, ENGG and CS SELECT * INTO fee_rec from fee_details where fee_name like 'EDP%' AND fee_no like 'TF%'; IF TF3 > 0 THEN INSERT INTO assessment_details values(STUD_ID,fee_rec.fee_no,fee_rec.acct_code,sdate,stime,TF3,TF3); commit; END IF; CLOSE subj_cur; -- Save Laboratory Fee Details SELECT * INTO lab_rec from fee_details where fee_no = 'LF009'; IF LF9 > 0 THEN INSERT INTO assessment_details values(STUD_ID,lab_rec.fee_no,lab_rec.acct_code,sdate,stime,LF9,LF9); commit; END IF; SELECT * INTO lab_rec from fee_details where fee_no = 'LF010'; IF LF10 > 0 THEN INSERT INTO assessment_details values(STUD_ID,lab_rec.fee_no,lab_rec.acct_code,sdate,stime,LF10,LF10); commit; END IF; SELECT * INTO lab_rec from fee_details where fee_no = 'LF011'; IF LF11 > 0 THEN INSERT INTO assessment_details values(STUD_ID,lab_rec.fee_no,lab_rec.acct_code,sdate,stime,LF11,LF11); commit; END IF; SELECT * INTO lab_rec from fee_details where fee_no = 'LF012'; IF LF12 > 0 THEN INSERT INTO assessment_details values(STUD_ID,lab_rec.fee_no,lab_rec.acct_code,sdate,stime,LF12,LF12); commit; END IF; SELECT * INTO lab_rec from fee_details where fee_no = 'LF013'; IF LF13 > 0 THEN INSERT INTO assessment_details values(STUD_ID,lab_rec.fee_no,lab_rec.acct_code,sdate,stime,LF13,LF13); commit; END IF; SELECT * INTO lab_rec from fee_details where fee_no = 'LF014'; IF LF14 > 0 THEN INSERT INTO assessment_details values(STUD_ID,lab_rec.fee_no,lab_rec.acct_code,sdate,stime,LF14,LF14); commit; END IF; SELECT * INTO lab_rec from fee_details where fee_no = 'LF015'; IF LF15 > 0 THEN INSERT INTO assessment_details values(STUD_ID,lab_rec.fee_no,lab_rec.acct_code,sdate,stime,LF15,LF15); commit; END IF; SELECT * INTO lab_rec from fee_details where fee_no = 'LF016'; IF LF16 > 0 THEN INSERT INTO assessment_details values(STUD_ID,lab_rec.fee_no,lab_rec.acct_code,sdate,stime,LF16,LF16); commit; END IF; SELECT * INTO lab_rec from fee_details where fee_no = 'LF017'; IF LF17 > 0 THEN INSERT INTO assessment_details values(STUD_ID,lab_rec.fee_no,lab_rec.acct_code,sdate,stime,LF17,LF17); commit; END IF; SELECT * INTO lab_rec from fee_details where fee_no = 'LF018'; IF LF18 > 0 THEN INSERT INTO assessment_details values(STUD_ID,lab_rec.fee_no,lab_rec.acct_code,sdate,stime,LF18,LF18); commit; END IF; SELECT * INTO lab_rec from fee_details where fee_no = 'LF019'; IF LF19 > 0 THEN INSERT INTO assessment_details values(STUD_ID,lab_rec.fee_no,lab_rec.acct_code,sdate,stime,LF19,LF19); commit; END IF; SELECT * INTO lab_rec from fee_details where fee_no = 'LF020'; IF LF20 > 0 THEN INSERT INTO assessment_details values(STUD_ID,lab_rec.fee_no,lab_rec.acct_code,sdate,stime,LF20,LF20); commit; END IF; SELECT * INTO lab_rec from fee_details where fee_no = 'LF021'; IF LF21 > 0 THEN INSERT INTO assessment_details values(STUD_ID,lab_rec.fee_no,lab_rec.acct_code,sdate,stime,LF21,LF21); commit; END IF; SELECT * INTO lab_rec from fee_details where fee_no = 'LF022'; IF LF22 > 0 THEN INSERT INTO assessment_details values(STUD_ID,lab_rec.fee_no,lab_rec.acct_code,sdate,stime,LF22,LF22); commit; END IF; SELECT * INTO lab_rec from fee_details where fee_no = 'LF023'; IF LF23 > 0 THEN INSERT INTO assessment_details values(STUD_ID,lab_rec.fee_no,lab_rec.acct_code,sdate,stime,LF23,LF23); commit; END IF; SELECT * INTO lab_rec from fee_details where fee_no = 'LF024'; IF LF24 > 0 THEN INSERT INTO assessment_details values(STUD_ID,lab_rec.fee_no,lab_rec.acct_code,sdate,stime,LF24,LF24); commit; END IF; SELECT * INTO lab_rec from fee_details where fee_no = 'LF025'; IF LF25 > 0 THEN INSERT INTO assessment_details values(STUD_ID,lab_rec.fee_no,lab_rec.acct_code,sdate,stime,LF25,LF25); commit; END IF; SELECT * INTO lab_rec from fee_details where fee_no = 'LF026'; IF LF26 > 0 THEN INSERT INTO assessment_details values(STUD_ID,lab_rec.fee_no,lab_rec.acct_code,sdate,stime,LF26,LF26); commit; END IF; SELECT * INTO lab_rec from fee_details where fee_no = 'LF027'; IF LF27 > 0 THEN INSERT INTO assessment_details values(STUD_ID,lab_rec.fee_no,lab_rec.acct_code,sdate,stime,LF27,LF27); commit; END IF; SELECT * INTO lab_rec from fee_details where fee_no = 'LF028'; IF LF28 > 0 THEN INSERT INTO assessment_details values(STUD_ID,lab_rec.fee_no,lab_rec.acct_code,sdate,stime,LF28,LF28); commit; END IF; SELECT * INTO lab_rec from fee_details where fee_no = 'LF029'; IF LF29 > 0 THEN INSERT INTO assessment_details values(STUD_ID,lab_rec.fee_no,lab_rec.acct_code,sdate,stime,LF29,LF29); commit; END IF; SELECT * INTO lab_rec from fee_details where fee_no = 'LF030'; IF LF30 > 0 THEN INSERT INTO assessment_details values(STUD_ID,lab_rec.fee_no,lab_rec.acct_code,sdate,stime,LF30,LF30); commit; END IF; SELECT * INTO lab_rec from fee_details where fee_no = 'LF031'; IF LF31 > 0 THEN INSERT INTO assessment_details values(STUD_ID,lab_rec.fee_no,lab_rec.acct_code,sdate,stime,LF31,LF31); commit; END IF; SELECT * INTO lab_rec from fee_details where fee_no = 'LF032'; IF LF32 > 0 THEN INSERT INTO assessment_details values(STUD_ID,lab_rec.fee_no,lab_rec.acct_code,sdate,stime,LF32,LF32); commit; END IF; -- Insert/Update data in Assessment table SELECT count(*) INTO rowcount FROM assessment WHERE student_id = STUD_ID; dbms_output.put_line('Rowcount: ' || rowcount || ' Student: ' || STUD_ID); IF rowcount > 0 THEN dbms_output.put_line('*** TABLE ASSESSMENT UPDATED!***'); SELECT * INTO a_rec FROM assessment WHERE student_id = STUD_ID; IF a_rec.total_amount > a_rec.balance THEN bal := total - (a_rec.total_amount - a_rec.balance); dbms_output.put_line(total || ' - (' || a_rec.total_amount || ' - ' || a_rec.balance || ') = ' || bal); ELSE bal := total; END IF; UPDATE assessment SET a_date = sdate, total_amount = total, a_time = stime, balance = bal WHERE student_id = STUD_ID; ELSE dbms_output.put_line('*** DATA INSERTED in TABLE ASSESSMENT!***'); INSERT INTO assessment(student_id, a_date, total_amount, a_time, balance) values (STUD_ID, sdate, total, stime, total); END IF; rowcount := 0; -- INSERT/UPDATE IN ASSESS_DATA TABLE SELECT count(*) INTO rowcount FROM assess_data WHERE student_id = STUD_ID; dbms_output.put_line('Rowcount: ' || rowcount || ' Student: ' || STUD_ID); IF rowcount > 0 THEN UPDATE assess_data SET other_fees=other_fee, other_asmt=other_assessment, lab_fee=lab_fee, tuition=tuition, total=total, option1=option1, option2=option2, option3=option3, option4=option4 WHERE student_id = STUD_ID; dbms_output.put_line('*** TABLE ASSESS_DATA UPDATED!***'); ELSE INSERT INTO assess_data VALUES(STUD_ID, other_fee, other_assessment, lab, tuition, total, option1, option2, option3, option4); dbms_output.put_line('*** DATA INSERTED in TABLE ASSESS_DATA!***'); END IF; commit; END;