DECLARE status1 boolean := false; course_id varchar2(10); semester number ; section varchar2(10); cnt_section number; course_level number; ddegree varchar2(50); ssem varchar2(50); old_intake varchar2(50); charsub number; SUBJECT VARCHAR2(50); panggil number; DEG_PROGRAM VARCHAR2(2); deg_branch varchar2(2); --CON_SEM VARCHAR2(10); a number (10,3); count_seminfo number; cnt10 number; CESDET_ID1 VARCHAR2(15); CESDET_STUD_ID1 VARCHAR2(15); CESDET_COURSE_ID1 VARCHAR2(10); CESDET_SEMESTER_ID1 NUMBER; CESDET_MARKS1 NUMBER(10,3); CESDET_ACT_MARKS1 NUMBER(10,3); CESDET_TUTOR_NAME1 VARCHAR2(100); CESDET_STATUS1 VARCHAR2(10); CESDET_STATUS_FINAL1 VARCHAR2(1); CESDET_STATUS_GRADE1 VARCHAR2(10); CESDET_OLD_MARKS1 NUMBER(10,3); CESDET_STATUS_LAB1 VARCHAR2(10); --SPACIAL_SEM NUMBER; COUNT_SEME NUMBER; CP_STUD_ID VARCHAR2(15); CP_NEW_DEGREE VARCHAR2(6); CP_NEW_INTAKE VARCHAR2(10); --INTAKE VARCHAR2(4); CHAR_NUM VARCHAR2(20); NUM NUMBER; NUM_COUNT NUMBER; LEN_NUM NUMBER; CHAR_BIL VARCHAR2(20); GET_DATA VARCHAR2(3); LEN_CHAR NUMBER; SUM_CHAR NUMBER; SHORT_CHAR VARCHAR2(20); NEW_STUD_ID VARCHAR2(15); COUNT_ID NUMBER; count_sem number; BRANCH_ID VARCHAR2(3); NEW_SEM NUMBER; cp_new_id VARCHAR2(15); STATUS varchar2(10); TAHU varchar2(50); TAHU1 varchar2(50); TAHU2 varchar2(50); TAHU3 varchar2(50); COUNT_INT NUMBER; COUNT_CPCR NUMBER; KIRA NUMBER; PARM_NO number; sum_PARM number; PP varchar2(50); KK varchar2(50); LL varchar2(50); MM varchar2(50); v_studid varchar2(15); v_stud_ic varchar2(12); id_year varchar2(2); id_intake varchar2(1); id_num number; id_stud number; id_change number; id_degree_level number; stud_sem number; pos1 number; temp_ss1 varchar2(2); temp_ss2 varchar2(2); cut_intake varchar2(8); v_intake varchar2(8); idegree varchar2(6); v_stud_intake varchar2(10); v_intake_date date; v_section varchar2(5); cnt_do number; v_cur_status varchar2(20); v_cur_ic varchar2(15); cnt_id number; cursor student_list is select stud_id, stud_name,stud_degree,stud_status,branch_id,stud_intake, stud_religion from student_profile where stud_status ='ACTIVE' and stud_degree = :CP_NEW_DEGREE and stud_program in ('1') and stud_id = :C_new_stud_id; cursor course_assg(old_intake varchar2, ssem varchar2,ddegree varchar2) is select * from course_seq where intake = old_intake and semester_id = ssem and degree = ddegree order by intake, course_level, seq asc ; CURSOR NP IS SELECT CP_STUD_ID, CP_SEM_ID, CP_COURSE_SECTION, CP_COURSE_ID FROM CP_NEW_COURSE WHERE CP_STUD_ID = :C_ID; CURSOR CP IS select CP_COURSEREGIS_SEMESTER_ID, CP_COURSEREGIS_COURSE_ID, CP_COURSEREGIS_COURSE_SECTION, CP_COURSEREGIS_STUD_ID, CP_COURSEREGIS_STATUS, CP_COURSEREGIS_GRADE, CP_COURSEREGIS_CREATED_BY, CP_COURSEREGIS_INVOICED, CP_COURSEREGIS_CREATED_DATE, cp_courseregis_mid_grade, cp_courseregis_point, cp_courseregis_mid_point, cp_courseregis_grade_point, cp_courseregis_tut_section, cp_courseregis_lab_section, CP_COURSE_EQUI from CP_COURSE_REGISTRATION WHERE CP_COURSEREGIS_STUD_ID = :C_ID; CURSOR CPCR IS SELECT CP_COURSE_ID, CP_STATUS, CP_CREATED_BY , CP_CREATED_DATE , CP_INVOICE, CP_INVOICE_NO, CP_COURSE_EQUI FROM CP_CREDIT_TRANS WHERE CP_STUD_ID = :C_ID; CURSOR CPNC IS SELECT CP_STUD_ID, CP_SEM_ID, CP_COURSE_SECTION, CP_COURSE_ID FROM CP_NEW_COURSE WHERE CP_STUD_ID = :C_ID; --************************************* CURSOR CR IS SELECT * from STUD_REL where STUD_ID = :C_ID; CURSOR SWE IS SELECT * FROM STUD_WORK_EXP WHERE STUD_ID = :C_ID; CURSOR SPB IS SELECT * FROM STUD_PROF_BODY WHERE STUD_ID = :C_ID; CURSOR SS IS SELECT * FROM STUD_SKILL WHERE STUD_ID = :C_ID; CURSOR SH IS SELECT * FROM STUD_HOBBY WHERE STUD_ID = :C_ID; CURSOR SA IS SELECT * FROM STUD_AWARD WHERE STUD_ID = :C_ID; CURSOR SR IS SELECT * FROM STUD_RESEARCH WHERE STUD_ID = :C_ID; CURSOR SP IS SELECT * FROM STUD_PUB WHERE STUD_ID = :C_ID; CURSOR SU IS SELECT * FROM STUDENT_UPDATE WHERE SU_STUD_ID = :C_ID; CURSOR SE IS SELECT * FROM STUD_EDUCATION WHERE EDU_STUD_ID = :C_ID; CURSOR SC IS SELECT * FROM STUD_CCU WHERE STUD_ID = :C_ID; CURSOR QSI IS SELECT * FROM STUD_QUA_DETAIL WHERE QUA_STUD_ID = :C_ID; --AMBIL DATA LAMA DALAM CESDETAIL CURSOR CD IS SELECT * FROM CES_DETAIL WHERE CESDET_STUD_ID = :C_ID; --CAPTURE MARKAH LOMPONG /* cursor get_data1 is SELECT courseregis_SEMESTER_ID, SEMESTER_STATUS_DESC, courseregis_course_id, courseregis_stud_id FROM SEMESTER_STATUS, course_registration WHERE SEMESTER_STATUS_DESC LIKE 'Current' and semester_status_id = (select courseregis_semester_id from stud_semester where semester_status_desc like 'Current') and courseregis_stud_id = :C_ID;*/ cursor get_data1 is SELECT courseregis_SEMESTER_ID, courseregis_course_id,courseregis_stud_id FROM course_registration WHERE courseregis_SEMESTER_ID = (select semester_status_id from semester_status where semester_status_desc like 'Current' and substr(semester_status_id,1,1) ='1') and courseregis_stud_id = :C_ID; --************************************* outstanding number; BEGIN IF :change_program.cp_status = 'RESET' THEN Message('You already reset this student!'); Message(' '); RAISE Form_Trigger_Failure; ELSIF :change_program.cp_status <> 'APPROVED' THEN Message('You haven''t approved this student change program yet! Cannot reset.'); Message(' '); RAISE Form_Trigger_Failure; ELSIF :change_program.cp_status = 'APPROVED' THEN /* check if student status. if not ACTIVE, cannot RESET! */ select stud_status into v_cur_status from student_profile where stud_id = :c_id; IF v_cur_status <> 'ACTIVE' THEN message('Sorry! You cannot change program for student with status '||v_cur_status||'!'); message(' '); RAISE Form_Trigger_Failure; END IF; /* END check if student status. if not ACTIVE, cannot RESET! */ /* check if student have another ID that ACTIVE. if have, cannot change program! */ select stud_ic_cur into v_cur_ic from student_profile where stud_id = :c_id; select count(stud_id) into cnt_id from student_profile where stud_ic_cur = v_cur_ic and stud_id <> :c_id and stud_status = 'ACTIVE'; IF cnt_id > 0 THEN message('Sorry! This student already have another ID with status ACTIVE. Cannot change program!'); message(' '); RAISE Form_Trigger_Failure; END IF; /* END check if student have another ID that ACTIVE. if have, cannot change program! */ /* added by hani on 14/12/2007 */ /* convert intake from eg: 1/07/08 to 10708 */ SELECT INSTR(:CP_NEW_INTAKE,'/', 1, 1) into pos1 FROM dual; -- position of '/' SELECT SUBSTR(:CP_NEW_INTAKE, 1, (pos1-1)) into temp_ss1 FROM dual; -- take '1' SELECT SUBSTR(:CP_NEW_INTAKE, (pos1+1)) into cut_intake FROM dual; -- take '07/08' SELECT INSTR(cut_intake, '/', 1, 1) into pos1 FROM dual; -- position of '/' SELECT SUBSTR(cut_intake, 1, (pos1-1)) into temp_ss2 FROM dual; -- take '07' SELECT SUBSTR(cut_intake, (pos1+1)) into cut_intake FROM dual; -- take '08' stud_sem := temp_ss1||temp_ss2||cut_intake; --stud_sem := 10708; /* END convert intake from eg: 1/07/08 to 10708 */ /* END added by hani on 14/12/2007 */ SELECT DEGREE_PROGRAM, degree_branch INTO DEG_PROGRAM, deg_branch FROM DEGREE_MAIN WHERE DEGREE_ID = :CP_NEW_DEGREE; SELECT count(stud_degree_old) INTO cnt_do FROM student_profile WHERE stud_id = :c_id; IF cnt_do > 0 THEN UPDATE student_profile SET stud_degree = stud_degree_old WHERE stud_id = :c_id; UPDATE student_profile SET stud_degree_old = '' WHERE stud_id = :c_id; END IF; /* SELECT CP_NEW_INTAKE INTO INTAKE FROM CHANGE_PROGRAM WHERE CP_STUD_ID = :C_ID; */ --message(DEG_PROGRAM ||'HANI TULIS'); --hani CON_SEM := DEG_PROGRAM||stud_sem; ---MODIFY ON 29-03-06 AFTER SERI' FINANCE MODULE COMPLETED /*if :CHANGE_PROGRAM.HUTANG > 0 then MESSAGE('Sorry, reset is not allow.There are outstanding unsettled fees for the current degree.', ACKNOWLEDGE); MESSAGE(' '); else */ --charsub := to_number(substr(:C_NEW_SEM,1,1)); charsub := to_number(DEG_PROGRAM); if charsub = 1 then :change_program.cp_verify_by := :pengguna; --:button_palette.d_username; :change_program.cp_verify_date :=:button_palette.d_date; --CON_SEM --IF :C_NEW_SEM IS NULL THEN /*hani IF CON_SEM IS NULL THEN MESSAGE('Please enter new semester ',acknowledge); END IF; hani*/ INSERT INTO STUD_PROFILE_CONV(SELECT * FROM STUDENT_PROFILE WHERE STUD_ID = :C_ID); --MESSAGE('a'); ---update old stud id to quit UPDATE STUDENT_PROFILE SET STUD_STATUS = 'CHANGE_PROGRAM' WHERE STUD_ID = :C_ID; UPDATE course_registration SET courseregis_grade = 'W' WHERE courseregis_grade is null AND courseregis_semester_id = stud_sem AND courseregis_stud_id = :C_ID; --MESSAGE('b'); /*SELECT CP_NEW_INTAKE INTO INTAKE FROM CHANGE_PROGRAM WHERE CP_STUD_ID = :C_ID; */ /* INTAKE := substr(:C_NEW_SEM,2,4); SELECT PARM_NO INTO PARM_NO FROM SYSPARM WHERE PARM_ID = 'CP_REF'; SUM_PARM := PARM_NO + 1; UPDATE SYSPARM SET PARM_NO = SUM_PARM WHERE PARM_ID = 'CP_REF'; */ UPDATE CHANGE_PROGRAM SET CP_NEW_INTAKE = :CP_NEW_INTAKE, CP_NO_REF = SUM_PARM WHERE CP_STUD_ID = :C_ID; --MESSAGE('c'); SELECT CP_NEW_DEGREE,CP_NEW_intake INTO CP_NEW_DEGREE,CP_NEW_intake FROM CHANGE_PROGRAM WHERE CP_STUD_ID = :C_ID; --MESSAGE('d'); UPDATE STUD_PROFILE_CONV SET STUD_DEGREE = CP_NEW_DEGREE, STUD_INTAKE = CP_NEW_intake WHERE CP_STUD_ID = :C_ID; --MESSAGE('e'); /*assign new id... tukar cara unisel */ select degree_level into id_degree_level from degree_main where degree_id = :CP_NEW_DEGREE; --select to_char(sysdate,'YY') into id_year from dual; id_num := 0; select max(substr(stud_id,5,5)) into id_num from student_profile where stud_intake = CP_NEW_intake; -- select stud_id_seq.NEXTVAL into id_num from dual; /* check if old id is NRIC, then NUM = 2. else, calculate last no. of id and + 1. */ select stud_ic_cur into v_stud_ic from student_profile where stud_id = :C_ID; IF v_stud_ic = :C_ID then NUM := 2; ELSE LEN_CHAR := length(:C_ID); SUM_CHAR := LEN_CHAR - 1; SHORT_CHAR := lpad (:C_ID, SUM_CHAR); CHAR_BIL := replace(:C_ID,SHORT_CHAR); NUM := TO_NUMBER(CHAR_BIL); NUM := NUM + 1; END IF; id_year := temp_ss2; id_intake := temp_ss1; id_change := NUM; id_stud := id_num + 1; v_studid := id_degree_level||id_year||id_intake||LPAD(id_stud,5,'0')||id_change; /*end assign new id... tukar cara unisel */ NEW_STUD_ID := v_studid; :C_NEW_STUD_ID := NEW_STUD_ID; SELECT COUNT(*) INTO COUNT_ID FROM STUDENT_PROFILE WHERE STUD_ID = NEW_STUD_ID; IF COUNT_ID = 0 THEN --MESSAGE('1'); UPDATE STUD_PROFILE_CONV SET STUD_ID = NEW_STUD_ID, STUD_DEGREE = CP_NEW_DEGREE, STUD_INTAKE = :CP_NEW_INTAKE, stud_create_user = :pengguna, stud_datetime = sysdate, branch_id = deg_branch WHERE STUD_ID = :C_ID; --MESSAGE('2'); INSERT INTO STUDENT_PROFILE(SELECT * FROM STUD_PROFILE_CONV WHERE STUD_ID = NEW_STUD_ID); --MESSAGE('2.2'); SELECT stud_intake INTO v_stud_intake FROM student_profile WHERE stud_id = :C_ID; SELECT sim_date INTO v_intake_date FROM stud_intake_main WHERE sim_id = :cp_new_intake; --MESSAGE('2.3'); IF :cp_new_intake <> v_stud_intake THEN UPDATE student_profile SET stud_intake_date = v_intake_date WHERE stud_id = NEW_STUD_ID; END IF; --MESSAGE('3'); DELETE FROM STUD_PROFILE_CONV WHERE STUD_ID = NEW_STUD_ID; --MESSAGE('4'); ---insert new id, new degree, new intake UPDATE STUDENT_PROFILE SET STUD_DEGREE = :CP_NEW_DEGREE, STUD_STATUS = 'ACTIVE' WHERE STUD_ID = NEW_STUD_ID; --MESSAGE('5'); SELECT BRANCH_ID INTO BRANCH_ID from STUDENT_PROFILE WHERE STUD_ID = NEW_STUD_ID; --BRANCH_ID := '02'; --CON_SEM := DEG_PROGRAM||INTAKE; NEW_SEM := stud_sem; --NEW_SEM := TO_NUMBER(:C_NEW_SEM); --MESSAGE('5.1'); --insert into stud_semester...to indicate that the student active for that semester. INSERT INTO stud_semester (studsms_stud_id, studsms_semester_id, studsms_status, studsms_degree, studsms_branch, daterecorded, studsms_create_user, studsms_remarks) VALUES (NEW_STUD_ID, NEW_SEM, 'ACTIVE', :CP_NEW_DEGREE, BRANCH_ID, sysdate, :PENGGUNA, 'NEW_PROGRAM'); --insert into stud_registration...to indicate that the student register that program 4 the 1st time. insert into stud_registration(str_stud_id, str_create_user, str_create_date, str_sem_id) values(NEW_STUD_ID, :PENGGUNA, sysdate, NEW_SEM); --MESSAGE('6'); v_section := 'CP-'||trim(deg_branch); UPDATE CP_NEW_COURSE SET CP_SEM_ID = NEW_SEM, CP_COURSE_SECTION = v_section WHERE CP_STUD_ID = :C_ID; --MESSAGE('7'); NUM_COUNT := 0; FOR A IN CPNC LOOP NUM_COUNT := NUM_COUNT + 1; END LOOP; SELECT NVL(COUNT(*),0) INTO COUNT_CPCR FROM CP_CREDIT_TRANS WHERE CP_STUD_ID = :C_ID; --SPACIAL_SEM := SUBSTR(CON_SEM,1,3)|| '0'; IF COUNT_CPCR > 0 THEN --MESSAGE('8'); FOR B IN CPCR LOOP ---insert cp_credit_trans w status approved into stud_credit_trans with cp_course_equi IF B.CP_STATUS = 'APPROVED' THEN INSERT INTO STUD_CREDIT_TRANS ( SCT_STUD_ID, SCT_COURSE_ID, SCT_CREATE_USER, SCT_CREATE_DATE, SCT_SEM_ID, SCT_STATUS, SCT_INVOICE, SCT_INVOICE_NO ) VALUES (NEW_STUD_ID, B.CP_COURSE_ID, B.CP_CREATED_BY, B.CP_CREATED_DATE, --hani SPACIAL_SEM, stud_sem, B.CP_STATUS, 'N', B.CP_INVOICE_NO); END IF; END LOOP; --MESSAGE('9'); end if; --********************************************************************************************************************* --MESSAGE('10'); -- copy student data from cp_new_course to cr for c in NP loop --MESSAGE('10.1'); select count(*) into count_seminfo from semester_info where seminfo_semester_id = NEW_SEM and seminfo_course_id = C.CP_COURSE_ID and seminfo_section = v_section and seminfo_branch_id = branch_id; --MESSAGE('10.2'); if count_seminfo = 0 then --MESSAGE('10.3'); insert into SEMESTER_INFO (seminfo_semester_id, seminfo_course_id,seminfo_section,seminfo_branch_id) values(NEW_SEM, C.CP_COURSE_ID, v_section, branch_id); --MESSAGE('10.4'); INSERT INTO COURSE_REGISTRATION ( COURSEREGIS_SEMESTER_ID, COURSEREGIS_COURSE_ID, COURSEREGIS_COURSE_SECTION, COURSEREGIS_STUD_ID) VALUES ( NEW_SEM, C.CP_COURSE_ID, v_section, NEW_STUD_ID); --MESSAGE('10.5'); else --MESSAGE('10.6'); INSERT INTO COURSE_REGISTRATION ( COURSEREGIS_SEMESTER_ID, COURSEREGIS_COURSE_ID, COURSEREGIS_COURSE_SECTION, COURSEREGIS_STUD_ID) VALUES ( NEW_SEM, C.CP_COURSE_ID, C.CP_COURSE_SECTION, NEW_STUD_ID); --MESSAGE('10.7'); end if; --MESSAGE('10.8'); end loop; --MESSAGE('11'); --********************************************************************************************************************* FOR M IN SE LOOP INSERT INTO STUD_EDUCATION ( EDU_STUD_ID, EDU_TYPE, EDU_AWARD, EDU_YEAR_FROM, EDU_YEAR_END, EDU_SPECIALIZATION, EDU_INSTITUTION ) VALUES ( NEW_STUD_ID, M.EDU_TYPE, M.EDU_AWARD, M.EDU_YEAR_FROM, M.EDU_YEAR_END, M.EDU_SPECIALIZATION, M.EDU_INSTITUTION); END LOOP; --MESSAGE('12'); --********************************************************************************************************************** FOR K IN QSI LOOP INSERT INTO STUD_QUA_DETAIL ( QUA_STUD_ID, QUA_TYPE, QUA_SUBJECT, QUA_GRADE) VALUES (NEW_STUD_ID, K.QUA_TYPE, K.QUA_SUBJECT, K.QUA_GRADE ); END LOOP; --MESSAGE('13'); --*********************************************************************************************************************** FOR L IN SC LOOP INSERT INTO STUD_CCU ( STUD_ID, STUD_CCU_ID, STUD_CCU_ACT, STUD_CCU_ORG, STUD_CCU_ROLE, STUD_CCU_START, STUD_CCU_END, STUD_CCU_CURRENT, STUD_UPD_BRANCH, STUD_DATETIME ) VALUES (NEW_STUD_ID, L.STUD_CCU_ID, L.STUD_CCU_ACT, L.STUD_CCU_ORG, L.STUD_CCU_ROLE, L.STUD_CCU_START, L.STUD_CCU_END, L.STUD_CCU_CURRENT, L.STUD_UPD_BRANCH, L.STUD_DATETIME ); END LOOP; --MESSAGE('14'); --********************************************************************************************************************* FOR N IN SU LOOP INSERT INTO STUDENT_UPDATE ( SU_SEQ, SU_STUD_ID, SU_BRANCH_ID, SU_STATUS, SU_INTAKE_DATE, SU_DEGREE, SU_YEAR, SU_PROGRAM, SU_IC_CUR, SU_REMARKS , SU_UPDATE, SU_USER, SU_UPD_BRANCH, SU_DATETIME ) VALUES ( N.SU_SEQ, NEW_STUD_ID, N.SU_BRANCH_ID, N.SU_STATUS, N.SU_INTAKE_DATE, N.SU_DEGREE, N.SU_YEAR, N.SU_PROGRAM, N.SU_IC_CUR, N.SU_REMARKS , N.SU_UPDATE, N.SU_USER, N.SU_UPD_BRANCH, N.SU_DATETIME); END LOOP; --MESSAGE('15'); --********************************************************************************************************************** FOR O IN SP LOOP INSERT INTO STUD_PUB ( STUD_ID, STUD_PUB_ID, STUD_PUB_TITLE, STUD_PUB_DATE, STUD_PUB_BOOKNAME, STUD_PUB_PUBNAME, STUD_RESEARCH_TYPE, STUD_UPD_BRANCH, STUD_DATETIME) VALUES ( NEW_STUD_ID, O.STUD_PUB_ID, O.STUD_PUB_TITLE, O.STUD_PUB_DATE, O.STUD_PUB_BOOKNAME, O.STUD_PUB_PUBNAME, O.STUD_RESEARCH_TYPE, O.STUD_UPD_BRANCH, O.STUD_DATETIME); END LOOP; --MESSAGE('16'); --************************************************************************************************************ FOR P IN SR LOOP INSERT INTO STUD_RESEARCH ( STUD_ID, STUD_RESEARCH_ID, STUD_RESEARCH_TITLE, STUD_RESEARCH_START, STUD_RESEARCH_END, STUD_RESEARCH_STATUS, STUD_RESEARCH_TYPE, STUD_RESEARCH_PROJECTID, STUD_RESEARCH_UPD_BRANCH, STUD_RESEARCH_DATETIME, STUD_UPD_BRANCH, STUD_DATETIME, STUD_RESEARCH_USER) VALUES ( NEW_STUD_ID, P.STUD_RESEARCH_ID, P.STUD_RESEARCH_TITLE, P.STUD_RESEARCH_START, P.STUD_RESEARCH_END, P.STUD_RESEARCH_STATUS, P.STUD_RESEARCH_TYPE, P.STUD_RESEARCH_PROJECTID, P.STUD_RESEARCH_UPD_BRANCH, P.STUD_RESEARCH_DATETIME, P.STUD_UPD_BRANCH, P.STUD_DATETIME, P.STUD_RESEARCH_USER); END LOOP; --MESSAGE('17'); --******************************************************************************************************************** FOR Q IN SA LOOP INSERT INTO STUD_AWARD ( STUD_ID, STUD_AWD_ID, STUD_AWD_NAME, STUD_AWD_DATE, STUD_AWD_ORG, STUD_UPD_BRANCH, STUD_DATETIME) VALUES (NEW_STUD_ID, Q.STUD_AWD_ID, Q.STUD_AWD_NAME, Q.STUD_AWD_DATE, Q.STUD_AWD_ORG, Q.STUD_UPD_BRANCH, Q.STUD_DATETIME); END LOOP; --MESSAGE('18'); --********************************************************************************************************************* FOR R IN SH LOOP INSERT INTO STUD_HOBBY ( STUD_ID, STUD_HOBBY_ID, STUD_HOBBY_NAME, STUD_UPD_BRANCH, STUD_DATETIME) VALUES ( NEW_STUD_ID, R.STUD_HOBBY_ID, R.STUD_HOBBY_NAME, R.STUD_UPD_BRANCH, R.STUD_DATETIME ); END LOOP; --MESSAGE('19'); --*********************************************************************************************************************** FOR Y IN SS LOOP INSERT INTO STUD_SKILL (STUD_ID, STUD_SKILL_ID, STUD_SKILL_NAME, STUD_UPD_BRANCH, STUD_DATETIME ) VALUES ( NEW_STUD_ID, Y.STUD_SKILL_ID, Y.STUD_SKILL_NAME, Y.STUD_UPD_BRANCH, Y.STUD_DATETIME ); END LOOP; --MESSAGE('20'); --*********************************************************************************************************************** FOR S IN SPB LOOP INSERT INTO STUD_PROF_BODY ( STUD_ID, STUD_PROF_ID, STUD_PROF_ACT, STUD_PROF_ORG, STUD_PROF_ROLE, STUD_PROF_START, STUD_PROF_END, STUD_PROF_CURR, STUD_UPD_BRANCH, STUD_DATETIME) VALUES ( NEW_STUD_ID, S.STUD_PROF_ID, S.STUD_PROF_ACT, S.STUD_PROF_ORG, S.STUD_PROF_ROLE, S.STUD_PROF_START, S.STUD_PROF_END, S.STUD_PROF_CURR, S.STUD_UPD_BRANCH, S.STUD_DATETIME); END LOOP; --*********************************************************************************************************************** --MESSAGE('21'); FOR T IN SWE LOOP INSERT INTO STUD_WORK_EXP ( STUD_ID, STUD_WRK_ID, STUD_WRK_ORG, STUD_WRK_JOB, STUD_WRK_START, STUD_WRK_END, STUD_UPD_BRANCH, STUD_DATETIME, STUD_WRK_ORG_DESC, STUD_WORK_SBJ_DESC, STUD_WRK_DUR_YR, STUD_WRK_DUR_MTH) VALUES ( NEW_STUD_ID, T.STUD_WRK_ID, T.STUD_WRK_ORG, T.STUD_WRK_JOB, T.STUD_WRK_START, T.STUD_WRK_END, T.STUD_UPD_BRANCH, T.STUD_DATETIME, T.STUD_WRK_ORG_DESC, T.STUD_WORK_SBJ_DESC, T.STUD_WRK_DUR_YR, T.STUD_WRK_DUR_MTH); END LOOP; --MESSAGE('22'); --*********************************************************************************************************************** FOR U IN CR LOOP INSERT INTO STUD_REL (STUD_ID, STUD_REL_ID, STUD_REL_TYPE, STUD_REL_NAME, STUD_REL_IC_CUR, STUD_REL_IC_OLD, STUD_REL_PASSPORT, STUD_REL_CITIZEN, STUD_REL_RACE, STUD_REL_SEX, STUD_REL_ADDR, STUD_REL_CITY, STUD_REL_STATE, STUD_REL_COUNTRY, STUD_REL_PCODE, STUD_REL_JOB, STUD_REL_EMP, STUD_REL_SAL, STUD_REL_WORKTEL, STUD_REL_HOMETEL, STUD_UPD_BRANCH, STUD_DATETIME, STUD_REL_HP, STUD_REL_ADDR2) VALUES (NEW_STUD_ID, U.STUD_REL_ID , U.STUD_REL_TYPE, U.STUD_REL_NAME, U.STUD_REL_IC_CUR, U.STUD_REL_IC_OLD, U.STUD_REL_PASSPORT, U.STUD_REL_CITIZEN, U.STUD_REL_RACE, U.STUD_REL_SEX, U.STUD_REL_ADDR, U.STUD_REL_CITY, U.STUD_REL_STATE, U.STUD_REL_COUNTRY, U.STUD_REL_PCODE, U.STUD_REL_JOB, U.STUD_REL_EMP, U.STUD_REL_SAL, U.STUD_REL_WORKTEL, U.STUD_REL_HOMETEL, U.STUD_UPD_BRANCH , U.STUD_DATETIME, U.STUD_REL_HP, U.STUD_REL_ADDR2); END LOOP; --********************************************************************************************************************* --MESSAGE('22'); FOR V IN CD LOOP INSERT INTO CES_DETAIL (CESDET_ID, CESDET_STUD_ID, CESDET_COURSE_ID, CESDET_SEMESTER_ID, CESDET_MARKS, CESDET_ACT_MARKS, CESDET_TUTOR_NAME, CESDET_STATUS, CESDET_STATUS_FINAL, CESDET_STATUS_GRADE) VALUES ( V.CESDET_ID, NEW_STUD_ID, V.CESDET_COURSE_ID, V.CESDET_SEMESTER_ID, V.CESDET_MARKS, V.CESDET_ACT_MARKS, V.CESDET_TUTOR_NAME, V.CESDET_STATUS, V.CESDET_STATUS_FINAL, V.CESDET_STATUS_GRADE ); END LOOP; --MESSAGE('22'); --******************************************************************************************************************* /* for c in get_data1 loop select count(1) into cnt10 from ces_detail where CESDET_STUD_ID = c.courseregis_stud_id and CESDET_COURSE_ID = c.courseregis_course_id and CESDET_SEMESTER_ID = c.courseregis_SEMESTER_ID and CESDET_MARKS is not null; if cnt10 =1 then select CESDET_ID, CESDET_STUD_ID , CESDET_COURSE_ID, CESDET_SEMESTER_ID, CESDET_MARKS, CESDET_ACT_MARKS, CESDET_TUTOR_NAME , CESDET_STATUS, CESDET_STATUS_FINAL, CESDET_STATUS_GRADE, CESDET_OLD_MARKS, CESDET_STATUS_LAB into CESDET_ID1, CESDET_STUD_ID1 , CESDET_COURSE_ID1, CESDET_SEMESTER_ID1, CESDET_MARKS1, CESDET_ACT_MARKS1, CESDET_TUTOR_NAME1 , CESDET_STATUS1, CESDET_STATUS_FINAL1, CESDET_STATUS_GRADE1, CESDET_OLD_MARKS1, CESDET_STATUS_LAB1 from ces_detail where CESDET_STUD_ID = :C_ID and CESDET_COURSE_ID = c.courseregis_course_id and CESDET_SEMESTER_ID = c.courseregis_SEMESTER_ID and CESDET_MARKS is not null; insert into ces_detail( CESDET_ID, CESDET_STUD_ID , CESDET_COURSE_ID, CESDET_SEMESTER_ID, CESDET_MARKS, CESDET_ACT_MARKS, CESDET_TUTOR_NAME , CESDET_STATUS, CESDET_STATUS_FINAL, CESDET_STATUS_GRADE, CESDET_OLD_MARKS, CESDET_STATUS_LAB) values( CESDET_ID1, NEW_STUD_ID , CESDET_COURSE_ID1, CESDET_SEMESTER_ID1, CESDET_MARKS1, CESDET_ACT_MARKS1, CESDET_TUTOR_NAME1 , CESDET_STATUS1, CESDET_STATUS_FINAL1, CESDET_STATUS_GRADE1, CESDET_OLD_MARKS1, CESDET_STATUS_LAB1); end if; end loop; */ --********************************************************************************************************************* :CHANGE_PROGRAM.CP_STATUS :='RESET'; :CHANGE_PROGRAM.CP_NEW_ID := NEW_STUD_ID; --********************************************************************************************************************* --MESSAGE('23'); /*hani - each programme already tagged to which branch. so cannot choose branch! IF :ID_BRANCH IS NOT NULL THEN UPDATE STUDENT_PROFILE SET BRANCH_ID =:ID_BRANCH WHERE STUD_ID = NEW_STUD_ID; END IF; hani */ COMMIT; --MESSAGE('24'); :process_desc := 'UPDATING COMPLETED'; synchronize; END IF; else :PROCESS_DESC := 'This module only for open market'; end if; END IF; --ELSIF :change_program.cp_status = 'APPROVED' THEN --end if; END;