[Home] [Help]
1854: WHERE app_no = cp_appno;
1855:
1856: -- jchakrab added for Bug 5481963 - 06-Sep-2006
1857: -- Retrieves choice records for an applicant in configured cycle and current institution
1858: CURSOR cur_uc_app_choices(p_appno igs_uc_app_choices.app_no%TYPE,
1859: p_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE,
1860: p_inst_code igs_uc_app_choices.institute_code%TYPE) IS
1861: SELECT uacc.rowid,
1862: uacc.*
1855:
1856: -- jchakrab added for Bug 5481963 - 06-Sep-2006
1857: -- Retrieves choice records for an applicant in configured cycle and current institution
1858: CURSOR cur_uc_app_choices(p_appno igs_uc_app_choices.app_no%TYPE,
1859: p_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE,
1860: p_inst_code igs_uc_app_choices.institute_code%TYPE) IS
1861: SELECT uacc.rowid,
1862: uacc.*
1863: FROM igs_uc_app_choices uacc
1856: -- jchakrab added for Bug 5481963 - 06-Sep-2006
1857: -- Retrieves choice records for an applicant in configured cycle and current institution
1858: CURSOR cur_uc_app_choices(p_appno igs_uc_app_choices.app_no%TYPE,
1859: p_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE,
1860: p_inst_code igs_uc_app_choices.institute_code%TYPE) IS
1861: SELECT uacc.rowid,
1862: uacc.*
1863: FROM igs_uc_app_choices uacc
1864: WHERE uacc.app_no = p_appno
1859: p_ucas_cycle igs_uc_app_choices.ucas_cycle%TYPE,
1860: p_inst_code igs_uc_app_choices.institute_code%TYPE) IS
1861: SELECT uacc.rowid,
1862: uacc.*
1863: FROM igs_uc_app_choices uacc
1864: WHERE uacc.app_no = p_appno
1865: AND uacc.ucas_cycle = p_ucas_cycle
1866: AND uacc.institute_code = p_inst_code;
1867:
2354: (new_ivstark_rec.withdrawn = 'W' AND
2355: cur_uc_app_choices_rec.decision = 'U' AND
2356: cur_uc_app_choices_rec.reply = 'F') THEN
2357:
2358: igs_uc_app_choices_pkg.update_row(
2359: x_rowid => cur_uc_app_choices_rec.rowid
2360: ,x_app_choice_id => cur_uc_app_choices_rec.app_choice_id
2361: ,x_app_id => cur_uc_app_choices_rec.app_id
2362: ,x_app_no => cur_uc_app_choices_rec.app_no
2504: Purpose : For processing IVSTARC - Applicant Choices info. details from UCAS.
2505: Known limitations,enhancements,remarks:
2506: Change History
2507: Who When What
2508: arvsrini 05-MAR-04 Added code to check before inserting records to IGS_UC_APP_CHOICES whether there exists record with
2509: same institution code and system code but having choice number as 99.If there exists no records, then insert is performed
2510: If it exists, the record is updated using choice number as IGS_UC_ISTARC_INTS.CHOICENO. Also if there exists records
2511: in IGS_UC_TRANSACTIONS with choice_no = 99 then those records are also updated. Bug#3239860
2512: jchakrab 23-AUG-04 Modified for Bug# 3837871 - Update system_code of existing app_choice records in
2509: same institution code and system code but having choice number as 99.If there exists no records, then insert is performed
2510: If it exists, the record is updated using choice number as IGS_UC_ISTARC_INTS.CHOICENO. Also if there exists records
2511: in IGS_UC_TRANSACTIONS with choice_no = 99 then those records are also updated. Bug#3239860
2512: jchakrab 23-AUG-04 Modified for Bug# 3837871 - Update system_code of existing app_choice records in
2513: IGS_UC_APP_CHOICES with current cycle's system_code in IGS_UC_APLICANTS
2514: jbaber 15-Sep-05 Entryyear defaults if NULL for all systems
2515: anwest 29-May-06 Bug #5190520 UCTD320 - UCAS 2006 CLEARING ISSUES
2516: ******************************************************************/
2517:
2521: FROM igs_uc_istarc_ints ivstk
2522: WHERE record_status = 'N';
2523:
2524:
2525: CURSOR old_starc_cur(p_appno igs_uc_app_choices.app_no%TYPE,
2526: p_choiceno igs_uc_app_choices.choice_no%TYPE,
2527: p_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
2528: SELECT appl.rowid,
2529: appl.*
2522: WHERE record_status = 'N';
2523:
2524:
2525: CURSOR old_starc_cur(p_appno igs_uc_app_choices.app_no%TYPE,
2526: p_choiceno igs_uc_app_choices.choice_no%TYPE,
2527: p_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
2528: SELECT appl.rowid,
2529: appl.*
2530: FROM igs_uc_app_choices appl
2523:
2524:
2525: CURSOR old_starc_cur(p_appno igs_uc_app_choices.app_no%TYPE,
2526: p_choiceno igs_uc_app_choices.choice_no%TYPE,
2527: p_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
2528: SELECT appl.rowid,
2529: appl.*
2530: FROM igs_uc_app_choices appl
2531: WHERE appl.app_no = p_appno
2526: p_choiceno igs_uc_app_choices.choice_no%TYPE,
2527: p_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
2528: SELECT appl.rowid,
2529: appl.*
2530: FROM igs_uc_app_choices appl
2531: WHERE appl.app_no = p_appno
2532: AND appl.choice_no = p_choiceno
2533: AND appl.ucas_cycle = p_cycle;
2534:
2592: curr_inst_rec curr_inst_cur%ROWTYPE;
2593: old_starc_9_rec old_starc_cur%ROWTYPE;-- 29-MAY-2006 anwest Bug #5190520 UCTD320 - UCAS 2006 CLEARING ISSUES
2594:
2595: l_entry_year igs_uc_ucas_control.entry_year%TYPE;
2596: l_deferred igs_uc_app_choices.deferred%TYPE;
2597: l_entrymonth igs_uc_app_choices.entry_month%TYPE;
2598: l_app_choice_id igs_uc_app_choices.app_choice_id%TYPE; -- Place holder for App CHoice ID - Seq gen value.
2599: l_valid VARCHAR2(1); -- for holding fetch from cursors for rec exists check.
2600:
2593: old_starc_9_rec old_starc_cur%ROWTYPE;-- 29-MAY-2006 anwest Bug #5190520 UCTD320 - UCAS 2006 CLEARING ISSUES
2594:
2595: l_entry_year igs_uc_ucas_control.entry_year%TYPE;
2596: l_deferred igs_uc_app_choices.deferred%TYPE;
2597: l_entrymonth igs_uc_app_choices.entry_month%TYPE;
2598: l_app_choice_id igs_uc_app_choices.app_choice_id%TYPE; -- Place holder for App CHoice ID - Seq gen value.
2599: l_valid VARCHAR2(1); -- for holding fetch from cursors for rec exists check.
2600:
2601: BEGIN
2594:
2595: l_entry_year igs_uc_ucas_control.entry_year%TYPE;
2596: l_deferred igs_uc_app_choices.deferred%TYPE;
2597: l_entrymonth igs_uc_app_choices.entry_month%TYPE;
2598: l_app_choice_id igs_uc_app_choices.app_choice_id%TYPE; -- Place holder for App CHoice ID - Seq gen value.
2599: l_valid VARCHAR2(1); -- for holding fetch from cursors for rec exists check.
2600:
2601: BEGIN
2602:
2644: -- AppNo validation
2645: IF g_error_code IS NULL THEN
2646:
2647: -- validate Applicant record details in UCAS Applicants table.
2648: -- This is because record gets inserted into igs_uc_app_choices as part of
2649: -- IVSTARN processing and hence at this stage the record must exist.
2650: validate_applicant (new_ivstarc_rec.appno, g_error_code);
2651: END IF;
2652:
2686:
2687: ------------------------------------------------
2688: -- Point of Entry Derivation/defaulting
2689: ------------------------------------------------
2690: -- Derive Point of Entry. - validate that only Number values are passed to igs_uc_app_choices.point_of_entry field.
2691: -- For any other values the entry point should be defaulted to 1.
2692: IF (ASCII(new_ivstarc_rec.entrypoint) >= 48 AND ASCII(new_ivstarc_rec.entrypoint) <= 57) OR new_ivstarc_rec.entrypoint IS NULL THEN
2693: new_ivstarc_rec.entrypoint := TO_NUMBER(new_ivstarc_rec.entrypoint); --
2694: ELSE
2770: g_error_code := '1045'; -- UCAS Course not found
2771: END IF;
2772: CLOSE get_oss_prog_cur;
2773:
2774: -- Added code to check before inserting records to IGS_UC_APP_CHOICES whether there exists record with
2775: -- same institution code and system code but having choice number as 99.If there exists no records, then insert is performed. arvsrini bug# 3239860
2776:
2777: OPEN curr_inst_cur(get_appl_dets_rec.system_code);
2778: FETCH curr_inst_cur INTO curr_inst_rec;
2797:
2798: BEGIN
2799:
2800: -- call the TBH to Insert new record
2801: igs_uc_app_choices_pkg.insert_row -- IGSXI02B.pls
2802: (
2803: x_rowid => old_starc_rec.rowid
2804: ,x_app_choice_id => l_app_choice_id
2805: ,x_app_id => get_appl_dets_rec.app_id
2970: IF g_error_code IS NULL THEN
2971:
2972: BEGIN
2973: -- call the TBH to update the record
2974: igs_uc_app_choices_pkg.update_row -- IGSXI02B.pls
2975: (
2976: x_rowid => old_starc_9_rec.rowid
2977: ,x_app_choice_id => old_starc_9_rec.app_choice_id
2978: ,x_app_id => old_starc_9_rec.app_id
3090: IF g_error_code IS NULL THEN
3091:
3092: BEGIN
3093: -- call the TBH to update the record
3094: igs_uc_app_choices_pkg.update_row -- IGSXI02B.pls
3095: (
3096: x_rowid => old_starc_rec.rowid
3097: ,x_app_choice_id => old_starc_rec.app_choice_id
3098: ,x_app_id => old_starc_rec.app_id
3245: info. details from UCAS.
3246: Known limitations,enhancements,remarks:
3247: Change History
3248: Who When What
3249: arvsrini 08-MAR-04 Added code to check before inserting records to IGS_UC_APP_CHOICES whether there exists record with
3250: same institution code and system code but having choice number as 99.If there exists no records, then insert is performed
3251: If it exists, the record is updated using choice number as IGS_UC_ISTARC_INTS.ROUNDNO. Also if there exists records
3252: in IGS_UC_TRANSACTIONS with choice_no = 99 then those records are also updated.Bug#3239860
3253: ******************************************************************/
3258: FROM igs_uc_istarg_ints ivstk
3259: WHERE record_status = 'N';
3260:
3261:
3262: CURSOR old_starg_cur (p_appno igs_uc_app_choices.app_no%TYPE,
3263: p_choiceno igs_uc_app_choices.choice_no%TYPE,
3264: p_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
3265: SELECT appl.rowid,
3266: appl.*
3259: WHERE record_status = 'N';
3260:
3261:
3262: CURSOR old_starg_cur (p_appno igs_uc_app_choices.app_no%TYPE,
3263: p_choiceno igs_uc_app_choices.choice_no%TYPE,
3264: p_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
3265: SELECT appl.rowid,
3266: appl.*
3267: FROM igs_uc_app_choices appl
3260:
3261:
3262: CURSOR old_starg_cur (p_appno igs_uc_app_choices.app_no%TYPE,
3263: p_choiceno igs_uc_app_choices.choice_no%TYPE,
3264: p_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
3265: SELECT appl.rowid,
3266: appl.*
3267: FROM igs_uc_app_choices appl
3268: WHERE appl.app_no = p_appno
3263: p_choiceno igs_uc_app_choices.choice_no%TYPE,
3264: p_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
3265: SELECT appl.rowid,
3266: appl.*
3267: FROM igs_uc_app_choices appl
3268: WHERE appl.app_no = p_appno
3269: AND appl.choice_no = p_choiceno
3270: AND appl.ucas_cycle = p_cycle;
3271:
3325: curr_inst_rec curr_inst_cur%ROWTYPE;
3326: old_starg_99_rec old_starg_cur%ROWTYPE; -- arvsrini uccr008
3327:
3328: l_entry_year igs_uc_ucas_control.entry_year%TYPE;
3329: l_deferred igs_uc_app_choices.deferred%TYPE;
3330: l_entrymonth igs_uc_app_choices.entry_month%TYPE;
3331: l_app_choice_id igs_uc_app_choices.app_choice_id%TYPE; -- Place holder for App CHoice ID - Seq gen value.
3332: l_valid VARCHAR2(1); -- for holding fetch from cursors for rec exists check.
3333:
3326: old_starg_99_rec old_starg_cur%ROWTYPE; -- arvsrini uccr008
3327:
3328: l_entry_year igs_uc_ucas_control.entry_year%TYPE;
3329: l_deferred igs_uc_app_choices.deferred%TYPE;
3330: l_entrymonth igs_uc_app_choices.entry_month%TYPE;
3331: l_app_choice_id igs_uc_app_choices.app_choice_id%TYPE; -- Place holder for App CHoice ID - Seq gen value.
3332: l_valid VARCHAR2(1); -- for holding fetch from cursors for rec exists check.
3333:
3334: BEGIN
3327:
3328: l_entry_year igs_uc_ucas_control.entry_year%TYPE;
3329: l_deferred igs_uc_app_choices.deferred%TYPE;
3330: l_entrymonth igs_uc_app_choices.entry_month%TYPE;
3331: l_app_choice_id igs_uc_app_choices.app_choice_id%TYPE; -- Place holder for App CHoice ID - Seq gen value.
3332: l_valid VARCHAR2(1); -- for holding fetch from cursors for rec exists check.
3333:
3334: BEGIN
3335:
3383: -- AppNo validation
3384: IF g_error_code IS NULL THEN
3385:
3386: -- validate Applicant record details in UCAS Applicants table.
3387: -- This is because record gets inserted into igs_uc_app_choices as part of
3388: -- IVSTARN processing and hence at this stage the record must exist.
3389: validate_applicant (new_ivstarg_rec.appno, g_error_code);
3390: END IF;
3391:
3588: END IF;
3589: CLOSE get_oss_prog_cur;
3590:
3591:
3592: --Added code to check before inserting records to IGS_UC_APP_CHOICES whether there exists record with
3593: --same institution code and system code but having choice number as 99.If there exists no records, then insert is performed
3594:
3595: OPEN curr_inst_cur(get_appl_dets_rec.system_code);
3596: FETCH curr_inst_cur INTO curr_inst_rec;
3611: IF g_error_code IS NULL THEN
3612:
3613: BEGIN
3614: -- call the TBH to Insert new record
3615: igs_uc_app_choices_pkg.insert_row -- IGSXI02B.pls
3616: (
3617: x_rowid => old_starg_rec.rowid
3618: ,x_app_choice_id => l_app_choice_id
3619: ,x_app_id => get_appl_dets_rec.app_id
3779: IF g_error_code IS NULL THEN
3780:
3781: BEGIN
3782: -- call the TBH to update the record
3783: igs_uc_app_choices_pkg.update_row -- IGSXI02B.pls
3784: (
3785: x_rowid => old_starg_99_rec.rowid
3786: ,x_app_choice_id => old_starg_99_rec.app_choice_id
3787: ,x_app_id => old_starg_99_rec.app_id
3897: IF g_error_code IS NULL THEN
3898:
3899: BEGIN
3900: -- call the TBH to update the record
3901: igs_uc_app_choices_pkg.update_row -- IGSXI02B.pls
3902: (
3903: x_rowid => old_starg_rec.rowid
3904: ,x_app_choice_id => old_starg_rec.app_choice_id
3905: ,x_app_id => old_starg_rec.app_id
4060: FROM igs_uc_istart_ints ivstt
4061: WHERE record_status = 'N';
4062:
4063:
4064: CURSOR old_start_cur(p_appno igs_uc_app_choices.app_no%TYPE) IS
4065: SELECT ucap.rowid,
4066: ucap.*
4067: FROM igs_uc_applicants ucap
4068: WHERE ucap.app_no = p_appno;
4105: -- AppNo validation
4106: ----------------------------
4107: IF g_error_code IS NULL THEN
4108: -- validate Applicant record details in UCAS Applicants table.
4109: -- This is because record gets inserted into igs_uc_app_choices as part of
4110: -- IVSTARN processing and hence at this stage the record must exist.
4111: validate_applicant (new_ivstart_rec.appno, g_error_code);
4112:
4113: END IF;
4319: FROM igs_uc_iqual_ints ivqual
4320: WHERE ivqual.record_status = 'N';
4321:
4322:
4323: CURSOR old_qual_cur(p_appno igs_uc_app_choices.app_no%TYPE) IS
4324: SELECT ucap.rowid,
4325: ucap.*
4326: FROM igs_uc_applicants ucap
4327: WHERE ucap.app_no = p_appno;
4364: ----------------------------
4365: -- AppNo validation
4366: ----------------------------
4367: -- validate Applicant record details in UCAS Applicants table.
4368: -- This is because record gets inserted into igs_uc_app_choices as part of
4369: -- IVSTARN processing and hence at this stage the record must exist.
4370: validate_applicant (new_ivqual_rec.appno, g_error_code);
4371: END IF;
4372:
4580: FROM igs_uc_istmnt_ints ivstmt
4581: WHERE ivstmt.record_status = 'N';
4582:
4583:
4584: CURSOR old_stmt_cur(p_appno igs_uc_app_choices.app_no%TYPE) IS
4585: SELECT ucap.rowid,
4586: ucap.*
4587: FROM igs_uc_applicants ucap
4588: WHERE ucap.app_no = p_appno;
4626: ----------------------------
4627: -- AppNo validation
4628: ----------------------------
4629: -- validate Applicant record details in UCAS Applicants table.
4630: -- This is because record gets inserted into igs_uc_app_choices as part of
4631: -- IVSTARN processing and hence at this stage the record must exist.
4632: validate_applicant (new_ivstmt_rec.appno, g_error_code);
4633:
4634: END IF;
4848: FROM igs_uc_ioffer_ints ivoff
4849: WHERE record_status = 'N';
4850:
4851:
4852: CURSOR old_offer_cur (p_appno igs_uc_app_choices.app_no%TYPE,
4853: p_choiceno igs_uc_app_choices.choice_no%TYPE,
4854: p_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
4855: SELECT appl.rowid,
4856: appl.*
4849: WHERE record_status = 'N';
4850:
4851:
4852: CURSOR old_offer_cur (p_appno igs_uc_app_choices.app_no%TYPE,
4853: p_choiceno igs_uc_app_choices.choice_no%TYPE,
4854: p_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
4855: SELECT appl.rowid,
4856: appl.*
4857: FROM igs_uc_app_choices appl
4850:
4851:
4852: CURSOR old_offer_cur (p_appno igs_uc_app_choices.app_no%TYPE,
4853: p_choiceno igs_uc_app_choices.choice_no%TYPE,
4854: p_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
4855: SELECT appl.rowid,
4856: appl.*
4857: FROM igs_uc_app_choices appl
4858: WHERE appl.app_no = p_appno
4853: p_choiceno igs_uc_app_choices.choice_no%TYPE,
4854: p_cycle igs_uc_app_choices.ucas_cycle%TYPE) IS
4855: SELECT appl.rowid,
4856: appl.*
4857: FROM igs_uc_app_choices appl
4858: WHERE appl.app_no = p_appno
4859: AND appl.choice_no = p_choiceno
4860: AND appl.ucas_cycle = p_cycle;
4861:
4859: AND appl.choice_no = p_choiceno
4860: AND appl.ucas_cycle = p_cycle;
4861:
4862: old_offer_rec old_offer_cur%ROWTYPE ; -- Holds the existing values for this incoming record.
4863: l_app_choice_id igs_uc_app_choices.app_choice_id%TYPE; -- Place holder for App CHoice ID - Seq gen value.
4864:
4865: BEGIN
4866:
4867: -- initialize variables
4901: -- AppNo validation
4902: IF g_error_code IS NULL THEN
4903:
4904: -- validate Applicant record details in UCAS Applicants table.
4905: -- This is because record gets inserted into igs_uc_app_choices as part of
4906: -- IVSTARN processing and hence at this stage the record must exist.
4907: validate_applicant (new_ivoffer_rec.appno, g_error_code);
4908: END IF;
4909:
4926: ELSE /* Update the record */
4927:
4928: BEGIN
4929: -- call the TBH to update the record
4930: igs_uc_app_choices_pkg.update_row -- IGSXI02B.pls
4931: (
4932: x_rowid => old_offer_rec.rowid
4933: ,x_app_choice_id => old_offer_rec.app_choice_id
4934: ,x_app_id => old_offer_rec.app_id
5074: FROM igs_uc_istarx_ints ivstx
5075: WHERE ivstx.record_status = 'N';
5076:
5077: -- check for corresponding record in main table.
5078: CURSOR old_starx_cur(p_appno igs_uc_app_choices.app_no%TYPE) IS
5079: SELECT uast.rowid,
5080: uast.*
5081: FROM igs_uc_app_stats uast
5082: WHERE uast.app_no = p_appno;
5183: ----------------------------
5184: -- AppNo validation
5185: ----------------------------
5186: -- validate Applicant record details in UCAS Applicants table.
5187: -- This is because record gets inserted into igs_uc_app_choices as part of
5188: -- IVSTARN processing and hence at this stage the record must exist.
5189: validate_applicant (new_ivstarx_rec.appno, g_error_code);
5190:
5191: END IF;
5383: FROM igs_uc_istarh_ints ivsth
5384: WHERE ivsth.record_status = 'N';
5385:
5386: -- check for corresponding record in main table.
5387: CURSOR old_starh_cur(p_appno igs_uc_app_choices.app_no%TYPE) IS
5388: SELECT uast.rowid,
5389: uast.*
5390: FROM igs_uc_app_stats uast
5391: WHERE uast.app_no = p_appno;
5489: ----------------------------
5490: -- AppNo validation
5491: ----------------------------
5492: -- validate Applicant record details in UCAS Applicants table.
5493: -- This is because record gets inserted into igs_uc_app_choices as part of
5494: -- IVSTARN processing and hence at this stage the record must exist.
5495: validate_applicant (new_ivstarh_rec.appno, g_error_code);
5496:
5497: END IF;
5686: Clearing info. details from UCAS.
5687: Known limitations,enhancements,remarks:
5688: Change History
5689: Who When What
5690: arvsrini 04-MAR-04 Added code to use IGS_UC_ISTARZ1_INTS record to update Choice Number 9 record in IGS_UC_APP_CHOICES when the
5691: IGS_UC_ISTARZ1_INTS.INST = Current Institution Code defined in UCAS Setup.
5692: modified wrt UCCR008 build. Bug#3239860
5693: anwest 29-MAY-06 Bug #5190520 UCTD320 - UCAS 2006 CLEARING ISSUES
5694: anwest 02-AUG-06 Bug #5440216 URGENT - UCAS CLEARING 2006 - PART 2 - CLEARING CHOICE NUMBER NULL
5741: FROM igs_uc_defaults
5742: WHERE system_code = p_sys_code;
5743:
5744: -- 02-AUG-2006 anwest Bug #5440216 URGENT - UCAS CLEARING 2006 - PART 2 - CLEARING CHOICE NUMBER NULL
5745: CURSOR uc_app_choices_cur(p_appno igs_uc_app_choices.app_no%TYPE) IS
5746: SELECT uacc.rowid,
5747: uacc.*
5748: FROM igs_uc_app_choices uacc
5749: WHERE uacc.app_no = p_appno
5744: -- 02-AUG-2006 anwest Bug #5440216 URGENT - UCAS CLEARING 2006 - PART 2 - CLEARING CHOICE NUMBER NULL
5745: CURSOR uc_app_choices_cur(p_appno igs_uc_app_choices.app_no%TYPE) IS
5746: SELECT uacc.rowid,
5747: uacc.*
5748: FROM igs_uc_app_choices uacc
5749: WHERE uacc.app_no = p_appno
5750: AND uacc.choice_no = 9;
5751:
5752:
5777: uc_app_choices_rec uc_app_choices_cur%ROWTYPE; --arvsrini UCCR008
5778: oss_prog_rec get_oss_prog_cur%ROWTYPE; -- Holds OSS Program details for the UCAS Course.
5779: curr_inst_rec curr_inst_cur%ROWTYPE;
5780:
5781: l_oss_program_code igs_uc_app_choices.oss_program_code%TYPE;
5782: l_oss_program_version igs_uc_app_choices.oss_program_version%TYPE;
5783: l_oss_attendance_type igs_uc_app_choices.oss_attendance_type%TYPE;
5784: l_oss_attendance_mode igs_uc_app_choices.oss_attendance_mode%TYPE;
5785: l_oss_location igs_uc_app_choices.oss_location%TYPE;
5778: oss_prog_rec get_oss_prog_cur%ROWTYPE; -- Holds OSS Program details for the UCAS Course.
5779: curr_inst_rec curr_inst_cur%ROWTYPE;
5780:
5781: l_oss_program_code igs_uc_app_choices.oss_program_code%TYPE;
5782: l_oss_program_version igs_uc_app_choices.oss_program_version%TYPE;
5783: l_oss_attendance_type igs_uc_app_choices.oss_attendance_type%TYPE;
5784: l_oss_attendance_mode igs_uc_app_choices.oss_attendance_mode%TYPE;
5785: l_oss_location igs_uc_app_choices.oss_location%TYPE;
5786: l_decision igs_uc_app_choices.decision%TYPE;
5779: curr_inst_rec curr_inst_cur%ROWTYPE;
5780:
5781: l_oss_program_code igs_uc_app_choices.oss_program_code%TYPE;
5782: l_oss_program_version igs_uc_app_choices.oss_program_version%TYPE;
5783: l_oss_attendance_type igs_uc_app_choices.oss_attendance_type%TYPE;
5784: l_oss_attendance_mode igs_uc_app_choices.oss_attendance_mode%TYPE;
5785: l_oss_location igs_uc_app_choices.oss_location%TYPE;
5786: l_decision igs_uc_app_choices.decision%TYPE;
5787: l_reply igs_uc_app_choices.reply%TYPE;
5780:
5781: l_oss_program_code igs_uc_app_choices.oss_program_code%TYPE;
5782: l_oss_program_version igs_uc_app_choices.oss_program_version%TYPE;
5783: l_oss_attendance_type igs_uc_app_choices.oss_attendance_type%TYPE;
5784: l_oss_attendance_mode igs_uc_app_choices.oss_attendance_mode%TYPE;
5785: l_oss_location igs_uc_app_choices.oss_location%TYPE;
5786: l_decision igs_uc_app_choices.decision%TYPE;
5787: l_reply igs_uc_app_choices.reply%TYPE;
5788:
5781: l_oss_program_code igs_uc_app_choices.oss_program_code%TYPE;
5782: l_oss_program_version igs_uc_app_choices.oss_program_version%TYPE;
5783: l_oss_attendance_type igs_uc_app_choices.oss_attendance_type%TYPE;
5784: l_oss_attendance_mode igs_uc_app_choices.oss_attendance_mode%TYPE;
5785: l_oss_location igs_uc_app_choices.oss_location%TYPE;
5786: l_decision igs_uc_app_choices.decision%TYPE;
5787: l_reply igs_uc_app_choices.reply%TYPE;
5788:
5789: -- 02-AUG-2006 anwest Bug #5440216 URGENT - UCAS CLEARING 2006 - PART 2 - CLEARING CHOICE NUMBER NULL
5782: l_oss_program_version igs_uc_app_choices.oss_program_version%TYPE;
5783: l_oss_attendance_type igs_uc_app_choices.oss_attendance_type%TYPE;
5784: l_oss_attendance_mode igs_uc_app_choices.oss_attendance_mode%TYPE;
5785: l_oss_location igs_uc_app_choices.oss_location%TYPE;
5786: l_decision igs_uc_app_choices.decision%TYPE;
5787: l_reply igs_uc_app_choices.reply%TYPE;
5788:
5789: -- 02-AUG-2006 anwest Bug #5440216 URGENT - UCAS CLEARING 2006 - PART 2 - CLEARING CHOICE NUMBER NULL
5790: l_campus_clr igs_uc_app_clearing.campus%TYPE;
5783: l_oss_attendance_type igs_uc_app_choices.oss_attendance_type%TYPE;
5784: l_oss_attendance_mode igs_uc_app_choices.oss_attendance_mode%TYPE;
5785: l_oss_location igs_uc_app_choices.oss_location%TYPE;
5786: l_decision igs_uc_app_choices.decision%TYPE;
5787: l_reply igs_uc_app_choices.reply%TYPE;
5788:
5789: -- 02-AUG-2006 anwest Bug #5440216 URGENT - UCAS CLEARING 2006 - PART 2 - CLEARING CHOICE NUMBER NULL
5790: l_campus_clr igs_uc_app_clearing.campus%TYPE;
5791: l_campus_chc igs_uc_app_choices.campus%TYPE;
5787: l_reply igs_uc_app_choices.reply%TYPE;
5788:
5789: -- 02-AUG-2006 anwest Bug #5440216 URGENT - UCAS CLEARING 2006 - PART 2 - CLEARING CHOICE NUMBER NULL
5790: l_campus_clr igs_uc_app_clearing.campus%TYPE;
5791: l_campus_chc igs_uc_app_choices.campus%TYPE;
5792:
5793:
5794:
5795: BEGIN
5832: ----------------------------
5833: -- AppNo validation
5834: ----------------------------
5835: -- validate Applicant record details in UCAS Applicants table.
5836: -- This is because record gets inserted into igs_uc_app_choices as part of
5837: -- IVSTARN processing and hence at this stage the record must exist.
5838: validate_applicant (new_ivstarz1_rec.appno, g_error_code);
5839:
5840: END IF;
6012:
6013: END IF; -- insert / update
6014:
6015:
6016: -- added following code to udpdate igs_uc_app_choices for records with choice no=9 w.r.t. build UCCR008 bug#3239860
6017:
6018: OPEN curr_inst_cur(appl_det_rec.system_code);
6019: FETCH curr_inst_cur INTO curr_inst_rec;
6020: CLOSE curr_inst_cur;
6095: END IF;
6096:
6097: BEGIN
6098: -- call the TBH to update the record
6099: igs_uc_app_choices_pkg.update_row
6100: (
6101: x_rowid => uc_app_choices_rec.rowid,
6102: x_app_choice_id => uc_app_choices_rec.app_choice_id,
6103: x_app_id => uc_app_choices_rec.app_id,
6382: -- AppNo validation
6383: ----------------------------
6384: IF g_error_code IS NULL THEN
6385: -- validate Applicant record details in UCAS Applicants table.
6386: -- This is because record gets inserted into igs_uc_app_choices as part of
6387: -- IVSTARN processing and hence at this stage the record must exist.
6388: validate_applicant (new_ivstarz2_rec.appno, g_error_code);
6389:
6390: END IF;
6721: -- AppNo validation
6722: ----------------------------
6723: IF g_error_code IS NULL THEN
6724: -- validate Applicant record details in UCAS Applicants table.
6725: -- This is because record gets inserted into igs_uc_app_choices as part of
6726: -- IVSTARN processing and hence at this stage the record must exist.
6727: validate_applicant (new_ivstarw_rec.appno, g_error_code);
6728:
6729: END IF;
7195: -- AppNo validation
7196: ----------------------------
7197: IF g_error_code IS NULL THEN
7198: -- validate Applicant record details in UCAS Applicants table.
7199: -- This is because record gets inserted into igs_uc_app_choices as part of
7200: -- IVSTARN processing and hence at this stage the record must exist.
7201: validate_applicant (new_ivfrmqual_rec.appno, g_error_code);
7202:
7203: END IF;
7493: ----------------------------
7494: -- AppNo validation
7495: ----------------------------
7496: -- validate Applicant record details in UCAS Applicants table.
7497: -- This is because record gets inserted into igs_uc_app_choices as part of
7498: -- IVSTARN processing and hence at this stage the record must exist.
7499: validate_applicant (new_ivstarpqr_rec.appno, g_error_code);
7500: -- If applicant exists in uc_applicants table then Proceed
7501: