1: PACKAGE BODY igs_ad_int_reconsider AS
2: /* $Header: IGSADD6B.pls 120.3 2006/05/26 07:17:44 pfotedar noship $ */
3:
4: FUNCTION copy_candidacy_records(p_new_admission_appl_number IGS_AD_APPL_ALL.admission_appl_number%TYPE,
5: p_new_sequence_number IGS_AD_PS_APPL_INST_ALL.sequence_number%TYPE,
6: p_person_id HZ_PARTIES.party_id%TYPE,
7: p_old_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE,
8: p_old_sequence_number IGS_AD_PS_APPL_INST_ALL.sequence_number%TYPE,
9: p_nominated_course_cd IGS_AD_PS_APPL_ALL.nominated_course_cd%TYPE,
4: FUNCTION copy_candidacy_records(p_new_admission_appl_number IGS_AD_APPL_ALL.admission_appl_number%TYPE,
5: p_new_sequence_number IGS_AD_PS_APPL_INST_ALL.sequence_number%TYPE,
6: p_person_id HZ_PARTIES.party_id%TYPE,
7: p_old_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE,
8: p_old_sequence_number IGS_AD_PS_APPL_INST_ALL.sequence_number%TYPE,
9: p_nominated_course_cd IGS_AD_PS_APPL_ALL.nominated_course_cd%TYPE,
10: p_start_dt DATE)
11: RETURN BOOLEAN AS
12:
5: p_new_sequence_number IGS_AD_PS_APPL_INST_ALL.sequence_number%TYPE,
6: p_person_id HZ_PARTIES.party_id%TYPE,
7: p_old_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE,
8: p_old_sequence_number IGS_AD_PS_APPL_INST_ALL.sequence_number%TYPE,
9: p_nominated_course_cd IGS_AD_PS_APPL_ALL.nominated_course_cd%TYPE,
10: p_start_dt DATE)
11: RETURN BOOLEAN AS
12:
13: /*******************************************************************************
418:
419: FUNCTION copy_application_child_records (p_person_id HZ_PARTIES.party_id%TYPE,
420: p_new_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE,
421: p_old_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE,
422: p_nominated_course_cd IGS_AD_PS_APPL_INST_ALL.nominated_course_cd%TYPE,
423: p_sequence_number IGS_AD_PS_APPL_INST_ALL.sequence_number%TYPE)
424: RETURN BOOLEAN IS
425:
426: -- other institutitons
419: FUNCTION copy_application_child_records (p_person_id HZ_PARTIES.party_id%TYPE,
420: p_new_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE,
421: p_old_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE,
422: p_nominated_course_cd IGS_AD_PS_APPL_INST_ALL.nominated_course_cd%TYPE,
423: p_sequence_number IGS_AD_PS_APPL_INST_ALL.sequence_number%TYPE)
424: RETURN BOOLEAN IS
425:
426: -- other institutitons
427:
425:
426: -- other institutitons
427:
428: CURSOR c_othinst_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
429: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE)
430: IS
431: SELECT
432: other_inst_id,
433: person_id ,
444:
445:
446: -- personal statements
447: CURSOR c_perstat_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
448: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE)
449: IS
450: SELECT
451: appl_perstat_id,
452: person_id ,
461:
462:
463: -- academic interests
464: CURSOR c_acadint_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
465: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE)
466: IS
467: SELECT
468: acad_interest_id ,
469: person_id ,
476: admission_appl_number = cp_admission_appl_number;
477:
478: --applicant intent
479: CURSOR c_appint_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
480: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE)
481: IS
482: SELECT
483: app_intent_id,
484: person_id ,
513:
514:
515: -- Special Interests
516: CURSOR c_splint_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
517: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE)
518: IS
519: SELECT
520: spl_interest_id ,
521: person_id ,
528: admission_appl_number = cp_admission_appl_number;
529:
530: --spl talents
531: CURSOR c_spltal_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
532: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE)
533: IS
534: SELECT
535: spl_talent_id ,
536: person_id ,
543: admission_appl_number = cp_admission_appl_number;
544:
545: -- Fees
546: CURSOR c_fee_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
547: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE
548: )IS
549: SELECT
550: app_req_id ,
551: person_id ,
751: RETURN FALSE;
752: END copy_application_child_records;
753:
754: FUNCTION copy_instance_child_records (p_new_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE,
755: p_new_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE,
756: p_person_id HZ_PARTIES.party_id%TYPE,
757: p_old_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE,
758: p_old_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE,
759: p_nominated_course_cd IGS_AD_PS_APPL.nominated_course_cd%TYPE,
754: FUNCTION copy_instance_child_records (p_new_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE,
755: p_new_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE,
756: p_person_id HZ_PARTIES.party_id%TYPE,
757: p_old_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE,
758: p_old_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE,
759: p_nominated_course_cd IGS_AD_PS_APPL.nominated_course_cd%TYPE,
760: p_start_dt DATE)
761: RETURN BOOLEAN IS
762: /*******************************************************************************
755: p_new_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE,
756: p_person_id HZ_PARTIES.party_id%TYPE,
757: p_old_admission_appl_number IGS_AD_APPL.admission_appl_number%TYPE,
758: p_old_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE,
759: p_nominated_course_cd IGS_AD_PS_APPL.nominated_course_cd%TYPE,
760: p_start_dt DATE)
761: RETURN BOOLEAN IS
762: /*******************************************************************************
763: Created by : Ramesh Rengarajan
773: *******************************************************************************/
774: -------------------------Cursor Declarations-------------------------------------------------------------------
775: -- Unitsets
776: CURSOR c_unitsets_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
777: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
778: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
779: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
780: IS
781: SELECT
774: -------------------------Cursor Declarations-------------------------------------------------------------------
775: -- Unitsets
776: CURSOR c_unitsets_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
777: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
778: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
779: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
780: IS
781: SELECT
782: unit_set_id,
775: -- Unitsets
776: CURSOR c_unitsets_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
777: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
778: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
779: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
780: IS
781: SELECT
782: unit_set_id,
783: person_id ,
798:
799:
800: -- education goals
801: CURSOR c_edugoal_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
802: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
803: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
804: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
805: IS
806: SELECT
799:
800: -- education goals
801: CURSOR c_edugoal_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
802: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
803: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
804: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
805: IS
806: SELECT
807: post_edugoal_id ,
800: -- education goals
801: CURSOR c_edugoal_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
802: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
803: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
804: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
805: IS
806: SELECT
807: post_edugoal_id ,
808: person_id ,
827:
828:
829: -- special consideration
830: CURSOR c_splcns_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
831: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
832: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
833: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
834: IS
835: SELECT
828:
829: -- special consideration
830: CURSOR c_splcns_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
831: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
832: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
833: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
834: IS
835: SELECT
836: spl_adm_cat_id ,
829: -- special consideration
830: CURSOR c_splcns_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
831: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
832: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
833: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
834: IS
835: SELECT
836: spl_adm_cat_id ,
837: person_id ,
849:
850:
851: -- Trackings
852: CURSOR c_tracking_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
853: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
854: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
855: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
856: IS
857: SELECT
850:
851: -- Trackings
852: CURSOR c_tracking_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
853: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
854: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
855: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
856: IS
857: SELECT
858: aplins_admreq_id ,
851: -- Trackings
852: CURSOR c_tracking_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
853: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
854: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
855: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
856: IS
857: SELECT
858: aplins_admreq_id ,
859: person_id ,
870: admission_appl_number = cp_admission_appl_number;
871:
872: -- program approval
873: CURSOR c_pgmapp_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
874: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
875: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
876: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
877: IS
878: SELECT
871:
872: -- program approval
873: CURSOR c_pgmapp_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
874: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
875: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
876: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
877: IS
878: SELECT
879: appl_pgmapprv_id ,
872: -- program approval
873: CURSOR c_pgmapp_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
874: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
875: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
876: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
877: IS
878: SELECT
879: appl_pgmapprv_id ,
880: person_id ,
896: admission_appl_number = cp_admission_appl_number;
897:
898: -- Test Scores
899: CURSOR c_test_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
900: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
901: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
902: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
903: IS
904: SELECT
897:
898: -- Test Scores
899: CURSOR c_test_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
900: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
901: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
902: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
903: IS
904: SELECT
905: tstscr_used_id ,
898: -- Test Scores
899: CURSOR c_test_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
900: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
901: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
902: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
903: IS
904: SELECT
905: tstscr_used_id ,
906: comments ,
939:
940:
941: -- notes
942: CURSOR c_notes_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
943: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
944: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
945: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
946: IS
947: SELECT
940:
941: -- notes
942: CURSOR c_notes_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
943: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
944: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
945: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
946: IS
947: SELECT
948: appl_notes_id ,
941: -- notes
942: CURSOR c_notes_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
943: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
944: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
945: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
946: IS
947: SELECT
948: appl_notes_id ,
949: person_id ,
960: sequence_number = cp_sequence_number and
961: admission_appl_number = cp_admission_appl_number;
962:
963: CURSOR c_evaluators_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
964: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
965: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
966: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
967:
968: IS
961: admission_appl_number = cp_admission_appl_number;
962:
963: CURSOR c_evaluators_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
964: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
965: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
966: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
967:
968: IS
969: SELECT
962:
963: CURSOR c_evaluators_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
964: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
965: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
966: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
967:
968: IS
969: SELECT
970: appl_eval_id,
991: admission_appl_number = cp_admission_appl_number;
992:
993:
994: CURSOR c_applrep_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
995: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
996: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
997: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
998:
999: IS
992:
993:
994: CURSOR c_applrep_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
995: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
996: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
997: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
998:
999: IS
1000: SELECT
993:
994: CURSOR c_applrep_cur(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
995: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
996: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
997: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE )
998:
999: IS
1000: SELECT
1001: appl_arp_id ,
1027: AND ccl.system_status = 'WAIVED'
1028: AND ccl.system_default = 'Y';
1029:
1030: CURSOR c_appl_qual_code(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
1031: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
1032: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
1033: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE ) IS
1034: SELECT qual.*
1035: FROM igs_ad_appqual_code qual
1028: AND ccl.system_default = 'Y';
1029:
1030: CURSOR c_appl_qual_code(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
1031: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
1032: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
1033: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE ) IS
1034: SELECT qual.*
1035: FROM igs_ad_appqual_code qual
1036: WHERE person_id = cp_person_id
1029:
1030: CURSOR c_appl_qual_code(cp_person_id HZ_PARTIES.PARTY_ID%TYPE,
1031: cp_nominated_course_cd IGS_AD_PS_APPL_INST.NOMINATED_COURSE_CD%TYPE,
1032: cp_admission_appl_number IGS_AD_PS_APPL_INST.admission_appl_number%TYPE,
1033: cp_sequence_number IGS_AD_PS_APPL_INST.sequence_number%TYPE ) IS
1034: SELECT qual.*
1035: FROM igs_ad_appqual_code qual
1036: WHERE person_id = cp_person_id
1037: AND nominated_course_cd = cp_nominated_course_cd
1037: AND nominated_course_cd = cp_nominated_course_cd
1038: AND sequence_number = cp_sequence_number
1039: AND admission_appl_number = cp_admission_appl_number;
1040:
1041: CURSOR c_intvw_dtls (cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
1042: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
1043: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nomINATED_COURSE_CD%TYPE,
1044: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE ) IS
1045: SELECT pndt.*
1038: AND sequence_number = cp_sequence_number
1039: AND admission_appl_number = cp_admission_appl_number;
1040:
1041: CURSOR c_intvw_dtls (cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
1042: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
1043: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nomINATED_COURSE_CD%TYPE,
1044: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE ) IS
1045: SELECT pndt.*
1046: FROM igs_ad_panel_dtls pndt
1039: AND admission_appl_number = cp_admission_appl_number;
1040:
1041: CURSOR c_intvw_dtls (cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
1042: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
1043: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nomINATED_COURSE_CD%TYPE,
1044: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE ) IS
1045: SELECT pndt.*
1046: FROM igs_ad_panel_dtls pndt
1047: WHERE person_id = cp_person_id
1040:
1041: CURSOR c_intvw_dtls (cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
1042: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
1043: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nomINATED_COURSE_CD%TYPE,
1044: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE ) IS
1045: SELECT pndt.*
1046: FROM igs_ad_panel_dtls pndt
1047: WHERE person_id = cp_person_id
1048: AND admission_appl_number = cp_admission_appl_number
1436: lv_sql_stmt VARCHAR(32767) ;
1437:
1438:
1439: /* This cursor returns the application to be processed for a given combination of person_id, current calendars and future calendars */
1440: CURSOR c_appl_inst(cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
1441: cp_fut_acad_cal_type igs_ad_ps_appl_inst_all.future_acad_cal_type%TYPE,
1442: cp_fut_acad_cal_seq_no igs_ad_ps_appl_inst_all.future_acad_ci_sequence_number%TYPE,
1443: cp_fut_adm_cal_type igs_ad_ps_appl_inst_all.future_adm_cal_type%TYPE,
1444: cp_fut_adm_cal_seq_no igs_ad_ps_appl_inst_all.future_adm_ci_sequence_number%TYPE,
1437:
1438:
1439: /* This cursor returns the application to be processed for a given combination of person_id, current calendars and future calendars */
1440: CURSOR c_appl_inst(cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
1441: cp_fut_acad_cal_type igs_ad_ps_appl_inst_all.future_acad_cal_type%TYPE,
1442: cp_fut_acad_cal_seq_no igs_ad_ps_appl_inst_all.future_acad_ci_sequence_number%TYPE,
1443: cp_fut_adm_cal_type igs_ad_ps_appl_inst_all.future_adm_cal_type%TYPE,
1444: cp_fut_adm_cal_seq_no igs_ad_ps_appl_inst_all.future_adm_ci_sequence_number%TYPE,
1445: cp_prev_acad_cal_type igs_ad_ps_appl_inst_all.future_acad_cal_type%TYPE,
1438:
1439: /* This cursor returns the application to be processed for a given combination of person_id, current calendars and future calendars */
1440: CURSOR c_appl_inst(cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
1441: cp_fut_acad_cal_type igs_ad_ps_appl_inst_all.future_acad_cal_type%TYPE,
1442: cp_fut_acad_cal_seq_no igs_ad_ps_appl_inst_all.future_acad_ci_sequence_number%TYPE,
1443: cp_fut_adm_cal_type igs_ad_ps_appl_inst_all.future_adm_cal_type%TYPE,
1444: cp_fut_adm_cal_seq_no igs_ad_ps_appl_inst_all.future_adm_ci_sequence_number%TYPE,
1445: cp_prev_acad_cal_type igs_ad_ps_appl_inst_all.future_acad_cal_type%TYPE,
1446: cp_prev_acad_cal_seq_no igs_ad_ps_appl_inst_all.future_acad_ci_sequence_number%TYPE,
1439: /* This cursor returns the application to be processed for a given combination of person_id, current calendars and future calendars */
1440: CURSOR c_appl_inst(cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
1441: cp_fut_acad_cal_type igs_ad_ps_appl_inst_all.future_acad_cal_type%TYPE,
1442: cp_fut_acad_cal_seq_no igs_ad_ps_appl_inst_all.future_acad_ci_sequence_number%TYPE,
1443: cp_fut_adm_cal_type igs_ad_ps_appl_inst_all.future_adm_cal_type%TYPE,
1444: cp_fut_adm_cal_seq_no igs_ad_ps_appl_inst_all.future_adm_ci_sequence_number%TYPE,
1445: cp_prev_acad_cal_type igs_ad_ps_appl_inst_all.future_acad_cal_type%TYPE,
1446: cp_prev_acad_cal_seq_no igs_ad_ps_appl_inst_all.future_acad_ci_sequence_number%TYPE,
1447: cp_prev_adm_cal_type igs_ad_ps_appl_inst_all.future_adm_cal_type%TYPE,
1440: CURSOR c_appl_inst(cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
1441: cp_fut_acad_cal_type igs_ad_ps_appl_inst_all.future_acad_cal_type%TYPE,
1442: cp_fut_acad_cal_seq_no igs_ad_ps_appl_inst_all.future_acad_ci_sequence_number%TYPE,
1443: cp_fut_adm_cal_type igs_ad_ps_appl_inst_all.future_adm_cal_type%TYPE,
1444: cp_fut_adm_cal_seq_no igs_ad_ps_appl_inst_all.future_adm_ci_sequence_number%TYPE,
1445: cp_prev_acad_cal_type igs_ad_ps_appl_inst_all.future_acad_cal_type%TYPE,
1446: cp_prev_acad_cal_seq_no igs_ad_ps_appl_inst_all.future_acad_ci_sequence_number%TYPE,
1447: cp_prev_adm_cal_type igs_ad_ps_appl_inst_all.future_adm_cal_type%TYPE,
1448: cp_prev_adm_cal_seq_no igs_ad_ps_appl_inst_all.future_adm_ci_sequence_number%TYPE,
1441: cp_fut_acad_cal_type igs_ad_ps_appl_inst_all.future_acad_cal_type%TYPE,
1442: cp_fut_acad_cal_seq_no igs_ad_ps_appl_inst_all.future_acad_ci_sequence_number%TYPE,
1443: cp_fut_adm_cal_type igs_ad_ps_appl_inst_all.future_adm_cal_type%TYPE,
1444: cp_fut_adm_cal_seq_no igs_ad_ps_appl_inst_all.future_adm_ci_sequence_number%TYPE,
1445: cp_prev_acad_cal_type igs_ad_ps_appl_inst_all.future_acad_cal_type%TYPE,
1446: cp_prev_acad_cal_seq_no igs_ad_ps_appl_inst_all.future_acad_ci_sequence_number%TYPE,
1447: cp_prev_adm_cal_type igs_ad_ps_appl_inst_all.future_adm_cal_type%TYPE,
1448: cp_prev_adm_cal_seq_no igs_ad_ps_appl_inst_all.future_adm_ci_sequence_number%TYPE,
1449: cp_admission_cat igs_ad_appl_all.admission_cat%TYPE,
1442: cp_fut_acad_cal_seq_no igs_ad_ps_appl_inst_all.future_acad_ci_sequence_number%TYPE,
1443: cp_fut_adm_cal_type igs_ad_ps_appl_inst_all.future_adm_cal_type%TYPE,
1444: cp_fut_adm_cal_seq_no igs_ad_ps_appl_inst_all.future_adm_ci_sequence_number%TYPE,
1445: cp_prev_acad_cal_type igs_ad_ps_appl_inst_all.future_acad_cal_type%TYPE,
1446: cp_prev_acad_cal_seq_no igs_ad_ps_appl_inst_all.future_acad_ci_sequence_number%TYPE,
1447: cp_prev_adm_cal_type igs_ad_ps_appl_inst_all.future_adm_cal_type%TYPE,
1448: cp_prev_adm_cal_seq_no igs_ad_ps_appl_inst_all.future_adm_ci_sequence_number%TYPE,
1449: cp_admission_cat igs_ad_appl_all.admission_cat%TYPE,
1450: cp_s_adm_process_type igs_ad_appl_all.s_admission_process_type%TYPE
1443: cp_fut_adm_cal_type igs_ad_ps_appl_inst_all.future_adm_cal_type%TYPE,
1444: cp_fut_adm_cal_seq_no igs_ad_ps_appl_inst_all.future_adm_ci_sequence_number%TYPE,
1445: cp_prev_acad_cal_type igs_ad_ps_appl_inst_all.future_acad_cal_type%TYPE,
1446: cp_prev_acad_cal_seq_no igs_ad_ps_appl_inst_all.future_acad_ci_sequence_number%TYPE,
1447: cp_prev_adm_cal_type igs_ad_ps_appl_inst_all.future_adm_cal_type%TYPE,
1448: cp_prev_adm_cal_seq_no igs_ad_ps_appl_inst_all.future_adm_ci_sequence_number%TYPE,
1449: cp_admission_cat igs_ad_appl_all.admission_cat%TYPE,
1450: cp_s_adm_process_type igs_ad_appl_all.s_admission_process_type%TYPE
1451: ) IS
1444: cp_fut_adm_cal_seq_no igs_ad_ps_appl_inst_all.future_adm_ci_sequence_number%TYPE,
1445: cp_prev_acad_cal_type igs_ad_ps_appl_inst_all.future_acad_cal_type%TYPE,
1446: cp_prev_acad_cal_seq_no igs_ad_ps_appl_inst_all.future_acad_ci_sequence_number%TYPE,
1447: cp_prev_adm_cal_type igs_ad_ps_appl_inst_all.future_adm_cal_type%TYPE,
1448: cp_prev_adm_cal_seq_no igs_ad_ps_appl_inst_all.future_adm_ci_sequence_number%TYPE,
1449: cp_admission_cat igs_ad_appl_all.admission_cat%TYPE,
1450: cp_s_adm_process_type igs_ad_appl_all.s_admission_process_type%TYPE
1451: ) IS
1452: SELECT acai.*,
1469: aprog.admission_cd,
1470: aprog.req_for_reconsideration_ind,
1471: aprog.req_for_adv_standing_ind
1472: FROM igs_ad_appl aa,
1473: igs_ad_ps_appl_inst acai,
1474: igs_ad_ou_stat aous,
1475: igs_ad_ps_appl aprog
1476: WHERE acai.adm_outcome_status = aous.adm_outcome_status
1477: AND aous.s_adm_outcome_status = 'PENDING'
1471: aprog.req_for_adv_standing_ind
1472: FROM igs_ad_appl aa,
1473: igs_ad_ps_appl_inst acai,
1474: igs_ad_ou_stat aous,
1475: igs_ad_ps_appl aprog
1476: WHERE acai.adm_outcome_status = aous.adm_outcome_status
1477: AND aous.s_adm_outcome_status = 'PENDING'
1478: AND acai.future_acad_cal_type = NVL(cp_fut_acad_cal_type, acai.future_acad_cal_type)
1479: AND acai.future_acad_ci_sequence_number = NVL(cp_fut_acad_cal_seq_no, acai.future_acad_ci_sequence_number)
1505:
1506:
1507: /* This cursor will return the application instance details for a given combination of
1508: person id, appl number, course cd and sequence number */
1509: CURSOR c_get_appl_instance (cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
1510: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
1511: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
1512: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE) IS
1513: SELECT acai.rowid,acai.*
1506:
1507: /* This cursor will return the application instance details for a given combination of
1508: person id, appl number, course cd and sequence number */
1509: CURSOR c_get_appl_instance (cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
1510: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
1511: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
1512: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE) IS
1513: SELECT acai.rowid,acai.*
1514: FROM igs_ad_ps_appl_inst_all acai
1507: /* This cursor will return the application instance details for a given combination of
1508: person id, appl number, course cd and sequence number */
1509: CURSOR c_get_appl_instance (cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
1510: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
1511: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
1512: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE) IS
1513: SELECT acai.rowid,acai.*
1514: FROM igs_ad_ps_appl_inst_all acai
1515: WHERE person_id = cp_person_id
1508: person id, appl number, course cd and sequence number */
1509: CURSOR c_get_appl_instance (cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
1510: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
1511: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
1512: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE) IS
1513: SELECT acai.rowid,acai.*
1514: FROM igs_ad_ps_appl_inst_all acai
1515: WHERE person_id = cp_person_id
1516: AND admission_appl_number = cp_admission_appl_number
1510: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
1511: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
1512: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE) IS
1513: SELECT acai.rowid,acai.*
1514: FROM igs_ad_ps_appl_inst_all acai
1515: WHERE person_id = cp_person_id
1516: AND admission_appl_number = cp_admission_appl_number
1517: AND nominated_course_cd = cp_nominated_course_cd
1518: AND sequence_number = cp_sequence_number;
1531: l_get_acad_cal_info c_get_acad_cal_info%ROWTYPE;
1532:
1533:
1534: /* This cursor will return the future calendars (if any) of an application instance*/
1535: CURSOR c_get_fut_acad_adm_cal_info (cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
1536: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
1537: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
1538: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE) IS
1539: SELECT future_acad_cal_type,future_acad_ci_sequence_number,future_adm_cal_type,future_adm_ci_sequence_number
1532:
1533:
1534: /* This cursor will return the future calendars (if any) of an application instance*/
1535: CURSOR c_get_fut_acad_adm_cal_info (cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
1536: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
1537: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
1538: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE) IS
1539: SELECT future_acad_cal_type,future_acad_ci_sequence_number,future_adm_cal_type,future_adm_ci_sequence_number
1540: FROM igs_ad_ps_appl_inst_all
1533:
1534: /* This cursor will return the future calendars (if any) of an application instance*/
1535: CURSOR c_get_fut_acad_adm_cal_info (cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
1536: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
1537: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
1538: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE) IS
1539: SELECT future_acad_cal_type,future_acad_ci_sequence_number,future_adm_cal_type,future_adm_ci_sequence_number
1540: FROM igs_ad_ps_appl_inst_all
1541: WHERE person_id = cp_person_id
1534: /* This cursor will return the future calendars (if any) of an application instance*/
1535: CURSOR c_get_fut_acad_adm_cal_info (cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
1536: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
1537: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
1538: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE) IS
1539: SELECT future_acad_cal_type,future_acad_ci_sequence_number,future_adm_cal_type,future_adm_ci_sequence_number
1540: FROM igs_ad_ps_appl_inst_all
1541: WHERE person_id = cp_person_id
1542: AND admission_appl_number = cp_admission_appl_number
1536: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
1537: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE,
1538: cp_sequence_number igs_ad_ps_appl_inst_all.sequence_number%TYPE) IS
1539: SELECT future_acad_cal_type,future_acad_ci_sequence_number,future_adm_cal_type,future_adm_ci_sequence_number
1540: FROM igs_ad_ps_appl_inst_all
1541: WHERE person_id = cp_person_id
1542: AND admission_appl_number = cp_admission_appl_number
1543: AND nominated_course_cd = cp_nominated_course_cd
1544: AND sequence_number = cp_sequence_number;
1544: AND sequence_number = cp_sequence_number;
1545:
1546: l_get_fut_acad_adm_cal_info c_get_fut_acad_adm_cal_info%ROWTYPE;
1547:
1548: CURSOR c_get_prog_dtls (cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
1549: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
1550: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE) IS
1551: SELECT aprog.*
1552: FROM igs_ad_ps_appl aprog
1545:
1546: l_get_fut_acad_adm_cal_info c_get_fut_acad_adm_cal_info%ROWTYPE;
1547:
1548: CURSOR c_get_prog_dtls (cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
1549: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
1550: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE) IS
1551: SELECT aprog.*
1552: FROM igs_ad_ps_appl aprog
1553: WHERE aprog.person_id = cp_person_id
1546: l_get_fut_acad_adm_cal_info c_get_fut_acad_adm_cal_info%ROWTYPE;
1547:
1548: CURSOR c_get_prog_dtls (cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
1549: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
1550: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE) IS
1551: SELECT aprog.*
1552: FROM igs_ad_ps_appl aprog
1553: WHERE aprog.person_id = cp_person_id
1554: AND aprog.admission_appl_number = cp_admission_appl_number
1548: CURSOR c_get_prog_dtls (cp_person_id igs_ad_ps_appl_inst_all.person_id%TYPE,
1549: cp_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE,
1550: cp_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE) IS
1551: SELECT aprog.*
1552: FROM igs_ad_ps_appl aprog
1553: WHERE aprog.person_id = cp_person_id
1554: AND aprog.admission_appl_number = cp_admission_appl_number
1555: AND aprog.nominated_course_cd = cp_nominated_course_cd;
1556:
1564: l_admission_type c_admission_type%ROWTYPE;
1565:
1566: l_new_admission_appl_number NUMBER;
1567:
1568: l_person_id igs_ad_ps_appl_inst_all.person_id%TYPE;
1569: l_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE;
1570: l_future_acad_cal_type igs_ad_ps_appl_inst_all.future_acad_cal_type%TYPE;
1571: l_fut_acad_ci_seq_no igs_ad_ps_appl_inst_all.future_acad_ci_sequence_number%TYPE;
1572: l_future_adm_cal_type igs_ad_ps_appl_inst_all.future_adm_cal_type%TYPE;
1565:
1566: l_new_admission_appl_number NUMBER;
1567:
1568: l_person_id igs_ad_ps_appl_inst_all.person_id%TYPE;
1569: l_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE;
1570: l_future_acad_cal_type igs_ad_ps_appl_inst_all.future_acad_cal_type%TYPE;
1571: l_fut_acad_ci_seq_no igs_ad_ps_appl_inst_all.future_acad_ci_sequence_number%TYPE;
1572: l_future_adm_cal_type igs_ad_ps_appl_inst_all.future_adm_cal_type%TYPE;
1573: l_fut_adm_ci_seq_no igs_ad_ps_appl_inst_all.future_adm_ci_sequence_number%TYPE;
1566: l_new_admission_appl_number NUMBER;
1567:
1568: l_person_id igs_ad_ps_appl_inst_all.person_id%TYPE;
1569: l_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE;
1570: l_future_acad_cal_type igs_ad_ps_appl_inst_all.future_acad_cal_type%TYPE;
1571: l_fut_acad_ci_seq_no igs_ad_ps_appl_inst_all.future_acad_ci_sequence_number%TYPE;
1572: l_future_adm_cal_type igs_ad_ps_appl_inst_all.future_adm_cal_type%TYPE;
1573: l_fut_adm_ci_seq_no igs_ad_ps_appl_inst_all.future_adm_ci_sequence_number%TYPE;
1574: l_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE;
1567:
1568: l_person_id igs_ad_ps_appl_inst_all.person_id%TYPE;
1569: l_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE;
1570: l_future_acad_cal_type igs_ad_ps_appl_inst_all.future_acad_cal_type%TYPE;
1571: l_fut_acad_ci_seq_no igs_ad_ps_appl_inst_all.future_acad_ci_sequence_number%TYPE;
1572: l_future_adm_cal_type igs_ad_ps_appl_inst_all.future_adm_cal_type%TYPE;
1573: l_fut_adm_ci_seq_no igs_ad_ps_appl_inst_all.future_adm_ci_sequence_number%TYPE;
1574: l_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE;
1575:
1568: l_person_id igs_ad_ps_appl_inst_all.person_id%TYPE;
1569: l_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE;
1570: l_future_acad_cal_type igs_ad_ps_appl_inst_all.future_acad_cal_type%TYPE;
1571: l_fut_acad_ci_seq_no igs_ad_ps_appl_inst_all.future_acad_ci_sequence_number%TYPE;
1572: l_future_adm_cal_type igs_ad_ps_appl_inst_all.future_adm_cal_type%TYPE;
1573: l_fut_adm_ci_seq_no igs_ad_ps_appl_inst_all.future_adm_ci_sequence_number%TYPE;
1574: l_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE;
1575:
1576: l_prev_acad_cal_type VARCHAR2(30);
1569: l_admission_appl_number igs_ad_ps_appl_inst_all.admission_appl_number%TYPE;
1570: l_future_acad_cal_type igs_ad_ps_appl_inst_all.future_acad_cal_type%TYPE;
1571: l_fut_acad_ci_seq_no igs_ad_ps_appl_inst_all.future_acad_ci_sequence_number%TYPE;
1572: l_future_adm_cal_type igs_ad_ps_appl_inst_all.future_adm_cal_type%TYPE;
1573: l_fut_adm_ci_seq_no igs_ad_ps_appl_inst_all.future_adm_ci_sequence_number%TYPE;
1574: l_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE;
1575:
1576: l_prev_acad_cal_type VARCHAR2(30);
1577: l_prev_acad_cal_seq_no NUMBER;
1570: l_future_acad_cal_type igs_ad_ps_appl_inst_all.future_acad_cal_type%TYPE;
1571: l_fut_acad_ci_seq_no igs_ad_ps_appl_inst_all.future_acad_ci_sequence_number%TYPE;
1572: l_future_adm_cal_type igs_ad_ps_appl_inst_all.future_adm_cal_type%TYPE;
1573: l_fut_adm_ci_seq_no igs_ad_ps_appl_inst_all.future_adm_ci_sequence_number%TYPE;
1574: l_nominated_course_cd igs_ad_ps_appl_inst_all.nominated_course_cd%TYPE;
1575:
1576: l_prev_acad_cal_type VARCHAR2(30);
1577: l_prev_acad_cal_seq_no NUMBER;
1578: l_prev_adm_cal_type VARCHAR2(30);
1808: IF NVL(l_get_prog_dtls.req_for_reconsideration_ind,'N') = 'Y' THEN
1809:
1810: BEGIN
1811:
1812: igs_ad_ps_appl_pkg.update_row(
1813: x_rowid => l_get_prog_dtls.row_id,
1814: x_person_id => l_get_prog_dtls.person_id,
1815: x_admission_appl_number => l_get_prog_dtls.admission_appl_number,
1816: x_nominated_course_cd => l_get_prog_dtls.nominated_course_cd,
2026: FUTURE_TERM_ADM_APPL_NUMBER and FUTURE_TERM_SEQUENCE_NUMBER
2027: to link with the new application instance*/
2028:
2029: IF l_instance_created = TRUE THEN
2030: igs_ad_ps_appl_inst_pkg.UPDATE_ROW (
2031: X_ROWID => l_appl_inst_rec.row_id,
2032: X_PERSON_ID => l_appl_inst_rec.person_id,
2033: X_ADMISSION_APPL_NUMBER => l_appl_inst_rec.ADMISSION_APPL_NUMBER,
2034: X_NOMINATED_COURSE_CD => l_appl_inst_rec.NOMINATED_COURSE_CD,
2167: l_appl_inst_rec.nominated_course_cd, l_sequence_number);
2168: FETCH c_get_appl_instance INTO l_get_appl_instance;
2169: CLOSE c_get_appl_instance;
2170:
2171: igs_ad_ps_appl_inst_pkg.UPDATE_ROW (
2172: X_ROWID => l_get_appl_instance.ROWID,
2173: X_PERSON_ID => l_get_appl_instance.PERSON_ID,
2174: X_ADMISSION_APPL_NUMBER => l_get_appl_instance.ADMISSION_APPL_NUMBER,
2175: X_NOMINATED_COURSE_CD => l_get_appl_instance.NOMINATED_COURSE_CD,
2529: IF NVL(l_get_prog_dtls.req_for_reconsideration_ind,'N') = 'Y' THEN
2530:
2531: BEGIN
2532:
2533: igs_ad_ps_appl_pkg.update_row(
2534: x_rowid => l_get_prog_dtls.row_id,
2535: x_person_id => l_get_prog_dtls.person_id,
2536: x_admission_appl_number => l_get_prog_dtls.admission_appl_number,
2537: x_nominated_course_cd => l_get_prog_dtls.nominated_course_cd,
2742: FUTURE_TERM_ADM_APPL_NUMBER and FUTURE_TERM_SEQUENCE_NUMBER
2743: to link with the new application instance*/
2744:
2745: IF l_instance_created = TRUE THEN
2746: igs_ad_ps_appl_inst_pkg.UPDATE_ROW (
2747: X_ROWID => l_appl_inst_rec.row_id,
2748: X_PERSON_ID => l_appl_inst_rec.person_id,
2749: X_ADMISSION_APPL_NUMBER => l_appl_inst_rec.ADMISSION_APPL_NUMBER,
2750: X_NOMINATED_COURSE_CD => l_appl_inst_rec.NOMINATED_COURSE_CD,
2888: /* Update the new application instance PREVIOUS_TERM_ADM_APPL_NUMBER and
2889: PREVIOUS_TERM_SEQUENCE_NUMBER columns to link it with the old application */
2890:
2891:
2892: igs_ad_ps_appl_inst_pkg.UPDATE_ROW (
2893: X_ROWID => l_get_appl_instance.ROWID,
2894: X_PERSON_ID => l_get_appl_instance.PERSON_ID,
2895: X_ADMISSION_APPL_NUMBER => l_get_appl_instance.ADMISSION_APPL_NUMBER,
2896: X_NOMINATED_COURSE_CD => l_get_appl_instance.NOMINATED_COURSE_CD,