[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT b.deceased_ind FROM igs_uc_applicants a,igs_pe_hz_parties b
WHERE a.app_no = P_App_no AND a.oss_person_id = b.party_id;
SELECT COUNT(*)
FROM igs_uc_transactions
WHERE app_no = P_App_no
AND choice_no = P_Choice_no
AND ucas_cycle = p_ucas_cycle
AND transaction_type = cp_transaction_type;
SELECT decision,reply FROM igs_uc_app_choices
WHERE app_no = P_App_no
AND choice_no = P_Choice_no
AND ucas_cycle = p_ucas_cycle;
SELECT COUNT(*) FROM igs_uc_transactions
WHERE app_no = P_App_no
AND ucas_cycle = p_ucas_cycle
AND transaction_type = 'RD';
SELECT COUNT(*) FROM igs_uc_u_cvname_2003
WHERE appno = P_App_no;
SELECT COUNT(*) FROM igs_uc_u_cvname_2003
WHERE appno =
(SELECT LPAD((app_no||check_digit),9,0) from IGS_UC_APPLICANTS where app_no = P_App_no);
SELECT COUNT(*) FROM igs_uc_applicants WHERE app_no = p_app_no;
SELECT COUNT(*) FROM igs_uc_app_choices
WHERE app_no = p_app_no AND (choice_no BETWEEN 1 AND 6)
AND ucas_cycle = p_ucas_cycle
AND institute_code = (SELECT current_inst_code FROM igs_uc_defaults WHERE system_code = p_system_code);
SELECT 'X' FROM igs_uc_app_choices
WHERE app_no = cp_appno
AND choice_no = cp_choice_no
AND ucas_cycle = cp_ucas_cycle
AND system_code = cp_system_code;
SELECT t.ROWID, t.* FROM igs_uc_transactions t
WHERE uc_tran_id = cp_uc_tran_id;
igs_uc_transactions_pkg.insert_row (
x_mode => p_mode, --jchakrab modified for 4380412
x_rowid => lv_rowid,
x_uc_tran_id => l_uc_tran_id,
x_transaction_id => NULL,
x_datetimestamp => NULL,
x_updater => NULL,
x_error_code => NULL,
x_transaction_type => P_Tran_type,
x_app_no => P_App_no,
x_choice_no => P_Choice_no,
x_decision => P_Decision,
x_program_code => P_Course,
x_campus => P_Campus,
x_entry_month => P_Entry_month,
x_entry_year => P_Entry_year,
x_entry_point => P_Entry_point,
x_soc => l_soc,
x_comments_in_offer => l_comments_in_offer,
x_return1 => l_return1,
x_return2 => l_return2,
x_hold_flag => l_hold,
x_sent_to_ucas => 'N',
x_test_cond_cat => P_cond_cat,
x_test_cond_name => P_cond_name,
x_inst_reference => P_Inst_reference ,
x_auto_generated_flag => P_auto_generated,
x_system_code => p_system_code,
x_ucas_cycle => p_ucas_cycle,
x_modular => p_modular,
x_part_time => p_part_time);
igs_uc_transactions_pkg.update_row(
x_rowid => l_transactions.rowid,
x_uc_tran_id => l_transactions.uc_tran_id,
x_transaction_id => l_transactions.transaction_id,
x_datetimestamp => l_transactions.datetimestamp,
x_updater => l_transactions.updater,
x_error_code => l_transactions.error_code,
x_transaction_type => l_transactions.transaction_type,
x_app_no => l_transactions.app_no,
x_choice_no => l_transactions.choice_no,
x_decision => l_transactions.decision,
x_program_code => l_transactions.program_code,
x_campus => l_transactions.campus,
x_entry_month => l_transactions.entry_month,
x_entry_year => l_transactions.entry_year,
x_entry_point => l_transactions.entry_point,
x_soc => l_transactions.soc ,
x_comments_in_offer => l_transactions.comments_in_offer,
x_return1 => l_transactions.return1,
x_return2 => l_transactions.return2,
x_hold_flag => 'Y',
x_sent_to_ucas => l_transactions.sent_to_ucas,
x_test_cond_cat => l_transactions.test_cond_cat,
x_test_cond_name => l_transactions.test_cond_name ,
x_mode => p_mode, --jchakrab modified for 4380412
x_inst_reference => l_transactions.inst_reference,
x_auto_generated_flag => l_transactions.auto_generated_flag,
x_system_code => l_transactions.system_code,
x_ucas_cycle => l_transactions.ucas_cycle,
x_modular => l_transactions.modular,
x_part_time => l_transactions.part_time);
igs_uc_transactions_pkg.insert_row (
x_mode => p_mode, --jchakrab modified for 4380412
x_rowid => lv_rowid,
x_uc_tran_id => l_uc_tran_id,
x_transaction_id => NULL,
x_datetimestamp => NULL,
x_updater => NULL,
x_error_code => NULL,
x_transaction_type => P_Tran_type,
x_app_no => P_App_no,
x_choice_no => P_Choice_no,
x_decision => p_decision,
x_program_code => P_Course,
x_campus => P_Campus,
x_entry_month => P_Entry_month,
x_entry_year => P_Entry_year,
x_entry_point => P_Entry_point,
x_soc => p_soc,
x_comments_in_offer => P_Free_Format,
x_return1 => NULL,
x_return2 => NULL,
x_hold_flag => l_hold,
x_sent_to_ucas => 'N',
x_test_cond_cat => NULL,
x_test_cond_name => NULL,
x_inst_reference => P_Inst_reference ,
x_auto_generated_flag => P_auto_generated,
x_system_code => p_system_code,
x_ucas_cycle => p_ucas_cycle,
x_modular => p_modular,
x_part_time => p_part_time);
igs_uc_transactions_pkg.insert_row (
x_mode => p_mode, --jchakrab modified for 4380412
x_rowid => lv_rowid,
x_uc_tran_id => l_uc_tran_id,
x_transaction_id => NULL,
x_datetimestamp => NULL,
x_updater => NULL,
x_error_code => NULL,
x_transaction_type => P_Tran_type,
x_app_no => P_App_no,
x_choice_no => P_Choice_no,
x_decision => P_Decision,
x_program_code => P_Course,
x_campus => P_Campus,
x_entry_month => P_Entry_month,
x_entry_year => P_Entry_year,
x_entry_point => P_Entry_point,
x_soc => l_soc,
x_comments_in_offer => l_comments_in_offer,
x_return1 => l_return1,
x_return2 => l_return2,
x_hold_flag => l_hold,
x_sent_to_ucas => 'N',
x_test_cond_cat => P_cond_cat,
x_test_cond_name => P_cond_name,
x_inst_reference => P_Inst_reference,
x_auto_generated_flag => P_auto_generated,
x_system_code => p_system_code,
x_ucas_cycle => p_ucas_cycle,
x_modular => p_modular,
x_part_time => p_part_time);
igs_uc_transactions_pkg.update_row(
x_rowid => l_transactions.rowid,
x_uc_tran_id => l_transactions.uc_tran_id,
x_transaction_id => l_transactions.transaction_id,
x_datetimestamp => l_transactions.datetimestamp,
x_updater => l_transactions.updater,
x_error_code => l_transactions.error_code,
x_transaction_type => l_transactions.transaction_type,
x_app_no => l_transactions.app_no,
x_choice_no => l_transactions.choice_no,
x_decision => l_transactions.decision,
x_program_code => l_transactions.program_code,
x_campus => l_transactions.campus,
x_entry_month => l_transactions.entry_month,
x_entry_year => l_transactions.entry_year,
x_entry_point => l_transactions.entry_point,
x_soc => l_transactions.soc ,
x_comments_in_offer => l_transactions.comments_in_offer,
x_return1 => l_transactions.return1,
x_return2 => l_transactions.return2,
x_hold_flag => 'Y',
x_sent_to_ucas => l_transactions.sent_to_ucas,
x_test_cond_cat => l_transactions.test_cond_cat,
x_test_cond_name => l_transactions.test_cond_name ,
x_mode => p_mode, --jchakrab modified for 4380412
x_inst_reference => l_transactions.inst_reference,
x_auto_generated_flag => l_transactions.auto_generated_flag,
x_system_code => l_transactions.system_code,
x_ucas_cycle => l_transactions.ucas_cycle,
x_modular => l_transactions.modular,
x_part_time => l_transactions.part_time);
igs_uc_transactions_pkg.insert_row (
x_mode => p_mode, --jchakrab modified for 4380412
x_rowid => lv_rowid,
x_uc_tran_id => l_uc_tran_id,
x_transaction_id => NULL,
x_datetimestamp => NULL,
x_updater => NULL,
x_error_code => NULL,
x_transaction_type => P_Tran_type,
x_app_no => P_App_no,
x_choice_no => P_Choice_no,
x_decision => NULL,
x_program_code => NULL,
x_campus => NULL,
x_entry_month => NULL,
x_entry_year => NULL,
x_entry_point => NULL,
x_soc => p_soc,
x_comments_in_offer => P_Free_Format,
x_return1 => NULL,
x_return2 => NULL,
x_hold_flag => l_hold,
x_sent_to_ucas => 'N',
x_test_cond_cat => NULL,
x_test_cond_name => NULL,
x_inst_reference => P_Inst_reference,
x_auto_generated_flag => P_auto_generated,
x_system_code => p_system_code,
x_ucas_cycle => p_ucas_cycle,
x_modular => p_modular,
x_part_time => p_part_time);
igs_uc_transactions_pkg.insert_row (
x_mode => p_mode, --jchakrab modified for 4380412
x_rowid => lv_rowid,
x_uc_tran_id => l_uc_tran_id,
x_transaction_id => NULL,
x_datetimestamp => NULL,
x_updater => NULL,
x_error_code => NULL,
x_transaction_type => P_Tran_type,
x_app_no => P_App_no,
x_choice_no => P_Choice_no,
x_decision => p_decision,
x_program_code => NULL,
x_campus => NULL,
x_entry_month => NULL,
x_entry_year => NULL,
x_entry_point => NULL,
x_soc => NULL,
x_comments_in_offer => NULL,
x_return1 => NULL,
x_return2 => NULL,
x_hold_flag => l_hold,
x_sent_to_ucas => 'N',
x_test_cond_cat => NULL ,
x_test_cond_name => NULL,
x_inst_reference => P_Inst_reference,
x_auto_generated_flag => P_auto_generated,
x_system_code => p_system_code,
x_ucas_cycle => p_ucas_cycle,
x_modular => p_modular,
x_part_time => p_part_time);
igs_uc_transactions_pkg.insert_row (
x_mode => p_mode, --jchakrab modified for 4380412
x_rowid => lv_rowid,
x_uc_tran_id => l_uc_tran_id,
x_transaction_id => NULL,
x_datetimestamp => NULL,
x_updater => NULL,
x_error_code => NULL,
x_transaction_type => P_Tran_type,
x_app_no => P_App_no,
x_choice_no => P_Choice_no,
x_decision => NULL,
x_program_code => P_Course,
x_campus => P_Campus,
x_entry_month => P_Entry_month,
x_entry_year => P_Entry_year,
x_entry_point => P_Entry_point,
x_soc => NULL,
x_comments_in_offer => NULL,
x_return1 => NULL,
x_return2 => NULL,
x_hold_flag => l_hold,
x_sent_to_ucas => 'N',
x_test_cond_cat => NULL,
x_test_cond_name => NULL,
x_inst_reference => P_Inst_reference,
x_auto_generated_flag => P_auto_generated,
x_system_code => p_system_code,
x_ucas_cycle => p_ucas_cycle,
x_modular => p_modular,
x_part_time => p_part_time);
igs_uc_transactions_pkg.insert_row (
x_mode => p_mode, --jchakrab modified for 4380412
x_rowid => lv_rowid,
x_uc_tran_id => l_uc_tran_id,
x_transaction_id => NULL,
x_datetimestamp => NULL,
x_updater => NULL,
x_error_code => NULL,
x_transaction_type => P_Tran_type,
x_app_no => P_App_no,
x_choice_no => P_Choice_no,
x_decision => P_Decision,
x_program_code => P_Course,
x_campus => P_Campus,
x_entry_month => P_Entry_month,
x_entry_year => P_Entry_year,
x_entry_point => P_Entry_point,
x_soc => NULL,
x_comments_in_offer => NULL,
x_return1 => NULL,
x_return2 => NULL,
x_hold_flag => l_hold,
x_sent_to_ucas => 'N',
x_test_cond_cat => NULL,
x_test_cond_name => NULL,
x_inst_reference => P_Inst_reference,
x_auto_generated_flag => P_auto_generated,
x_system_code => p_system_code,
x_ucas_cycle => p_ucas_cycle,
x_modular => p_modular,
x_part_time => p_part_time);
igs_uc_transactions_pkg.insert_row (
x_mode => p_mode, --jchakrab modified for 4380412
x_rowid => lv_rowid,
x_uc_tran_id => l_uc_tran_id,
x_transaction_id => NULL,
x_datetimestamp => NULL,
x_updater => NULL,
x_error_code => NULL,
x_transaction_type => P_Tran_type,
x_app_no => P_App_no,
x_choice_no => P_Choice_no,
x_decision => NULL,
x_program_code => P_Course,
x_campus => P_Campus,
x_entry_month => NULL,
x_entry_year => P_Entry_year,
x_entry_point => NULL,
x_soc => NULL,
x_comments_in_offer => NULL,
x_return1 => NULL,
x_return2 => NULL,
x_hold_flag => l_hold,
x_sent_to_ucas => 'N',
x_test_cond_cat => NULL,
x_test_cond_name => NULL,
x_inst_reference => P_Inst_reference,
x_auto_generated_flag => P_auto_generated,
x_system_code => p_system_code,
x_ucas_cycle => p_ucas_cycle,
x_modular => p_modular,
x_part_time => p_part_time);
igs_uc_transactions_pkg.insert_row (
x_mode => p_mode, --jchakrab modified for 4380412
x_rowid => lv_rowid,
x_uc_tran_id => l_uc_tran_id,
x_transaction_id => NULL,
x_datetimestamp => NULL,
x_updater => NULL,
x_error_code => NULL,
x_transaction_type => P_Tran_type,
x_app_no => P_App_no,
x_choice_no => P_Choice_no,
x_decision => NULL,
x_program_code => NULL,
x_campus => NULL,
x_entry_month => NULL,
x_entry_year => NULL,
x_entry_point => NULL,
x_soc => NULL,
x_comments_in_offer => NULL,
x_return1 => NULL,
x_return2 => NULL,
x_hold_flag => l_hold,
x_sent_to_ucas => 'N',
x_test_cond_cat => NULL,
x_test_cond_name => NULL,
x_inst_reference => P_Inst_reference,
x_auto_generated_flag => P_auto_generated,
x_system_code => p_system_code,
x_ucas_cycle => p_ucas_cycle,
x_modular => p_modular,
x_part_time => p_part_time);
igs_uc_transactions_pkg.insert_row (
x_mode => p_mode, --jchakrab modified for 4380412
x_rowid => lv_rowid,
x_uc_tran_id => l_uc_tran_id,
x_transaction_id => NULL,
x_datetimestamp => NULL,
x_updater => NULL,
x_error_code => NULL,
x_transaction_type => P_Tran_type,
x_app_no => P_App_no,
x_choice_no => P_Choice_no,
x_decision => NULL,
x_program_code => P_Course,
x_campus => P_Campus,
x_entry_month => NULL,
x_entry_year => P_Entry_year,
x_entry_point => NULL,
x_soc => NULL,
x_comments_in_offer => NULL,
x_return1 => NULL,
x_return2 => NULL,
x_hold_flag => l_hold,
x_sent_to_ucas => 'N',
x_test_cond_cat => NULL,
x_test_cond_name => NULL,
x_inst_reference => P_Inst_reference,
x_auto_generated_flag => P_auto_generated,
x_system_code => p_system_code,
x_ucas_cycle => p_ucas_cycle,
x_modular => p_modular,
x_part_time => p_part_time);
igs_uc_transactions_pkg.insert_row (
x_mode => p_mode, --jchakrab modified for 4380412
x_rowid => lv_rowid,
x_uc_tran_id => l_uc_tran_id,
x_transaction_id => NULL,
x_datetimestamp => NULL,
x_updater => NULL,
x_error_code => NULL,
x_transaction_type => P_Tran_type,
x_app_no => P_App_no,
x_choice_no => P_Choice_no,
x_decision => NULL,
x_program_code => NULL,
x_campus => NULL,
x_entry_month => NULL,
x_entry_year => NULL,
x_entry_point => NULL,
x_soc => NULL,
x_comments_in_offer => P_Free_Format,
x_return1 => NULL,
x_return2 => NULL,
x_hold_flag => l_hold,
x_sent_to_ucas => 'N',
x_test_cond_cat => NULL,
x_test_cond_name => NULL,
x_inst_reference => P_Inst_reference,
x_auto_generated_flag => P_auto_generated,
x_system_code => p_system_code,
x_ucas_cycle => p_ucas_cycle,
x_modular => p_modular,
x_part_time => p_part_time);
igs_uc_transactions_pkg.insert_row (
x_mode => p_mode, --jchakrab modified for 4380412
x_rowid => lv_rowid,
x_uc_tran_id => l_uc_tran_id,
x_transaction_id => NULL,
x_datetimestamp => NULL,
x_updater => NULL,
x_error_code => NULL,
x_transaction_type => P_Tran_type,
x_app_no => P_App_no,
x_choice_no => P_Choice_no,
x_decision => NULL,
x_program_code => NULL,
x_campus => NULL,
x_entry_month => NULL,
x_entry_year => NULL,
x_entry_point => NULL,
x_soc => NULL,
x_comments_in_offer => NULL,
x_return1 => NULL,
x_return2 => NULL,
x_hold_flag => l_hold,
x_sent_to_ucas => 'N',
x_test_cond_cat => NULL,
x_test_cond_name => NULL,
x_inst_reference => P_Inst_reference ,
x_auto_generated_flag => P_auto_generated,
x_system_code => p_system_code,
x_ucas_cycle => p_ucas_cycle,
x_modular => p_modular,
x_part_time => p_part_time);
igs_uc_transactions_pkg.insert_row (
x_mode => p_mode, --jchakrab modified for 4380412
x_rowid => lv_rowid,
x_uc_tran_id => l_uc_tran_id,
x_transaction_id => NULL,
x_datetimestamp => NULL,
x_updater => NULL,
x_error_code => NULL,
x_transaction_type => P_Tran_type,
x_app_no => P_App_no,
x_choice_no => P_Choice_no,
x_decision => P_Decision,
x_program_code => NVL(P_Course,NULL),
x_campus => P_Campus,
x_entry_month => P_Entry_month,
x_entry_year => P_Entry_year,
x_entry_point => P_Entry_point,
x_soc => NULL,
x_comments_in_offer => NULL,
x_return1 => NULL,
x_return2 => NULL,
x_hold_flag => l_hold,
x_sent_to_ucas => 'N',
x_test_cond_cat => NULL,
x_test_cond_name => NULL,
x_inst_reference => P_Inst_reference,
x_auto_generated_flag => P_auto_generated,
x_system_code => p_system_code,
x_ucas_cycle => p_ucas_cycle,
x_modular => p_modular,
x_part_time => p_part_time);
igs_uc_transactions_pkg.insert_row (
x_mode => p_mode, --jchakrab modified for 4380412
x_rowid => lv_rowid,
x_uc_tran_id => l_uc_tran_id,
x_transaction_id => NULL,
x_datetimestamp => NULL,
x_updater => NULL,
x_error_code => NULL,
x_transaction_type => P_Tran_type,
x_app_no => P_App_no,
x_choice_no => P_Choice_no,
x_decision => P_Decision,
x_program_code => P_Course,
x_campus => P_Campus,
x_entry_month => P_Entry_month,
x_entry_year => P_Entry_year,
x_entry_point => P_Entry_point,
x_soc => l_soc,
x_comments_in_offer => l_comments_in_offer,
x_return1 => l_return1,
x_return2 => l_return2,
x_hold_flag => l_hold,
x_sent_to_ucas => 'N',
x_test_cond_cat => P_cond_cat,
x_test_cond_name => P_cond_name,
x_inst_reference => P_Inst_reference,
x_auto_generated_flag => P_auto_generated,
x_system_code => p_system_code,
x_ucas_cycle => p_ucas_cycle,
x_modular => p_modular,
x_part_time => p_part_time);
igs_uc_transactions_pkg.update_row(
x_rowid => l_transactions.rowid,
x_uc_tran_id => l_transactions.uc_tran_id,
x_transaction_id => l_transactions.transaction_id,
x_datetimestamp => l_transactions.datetimestamp,
x_updater => l_transactions.updater,
x_error_code => l_transactions.error_code,
x_transaction_type => l_transactions.transaction_type,
x_app_no => l_transactions.app_no,
x_choice_no => l_transactions.choice_no,
x_decision => l_transactions.decision,
x_program_code => l_transactions.program_code,
x_campus => l_transactions.campus,
x_entry_month => l_transactions.entry_month,
x_entry_year => l_transactions.entry_year,
x_entry_point => l_transactions.entry_point,
x_soc => l_transactions.soc ,
x_comments_in_offer => l_transactions.comments_in_offer,
x_return1 => l_transactions.return1,
x_return2 => l_transactions.return2,
x_hold_flag => 'Y',
x_sent_to_ucas => l_transactions.sent_to_ucas,
x_test_cond_cat => l_transactions.test_cond_cat,
x_test_cond_name => l_transactions.test_cond_name ,
x_mode => p_mode, --jchakrab modified for 4380412
x_inst_reference => l_transactions.inst_reference,
x_auto_generated_flag => l_transactions.auto_generated_flag,
x_system_code => l_transactions.system_code,
x_ucas_cycle => l_transactions.ucas_cycle,
x_modular => l_transactions.modular,
x_part_time => l_transactions.part_time);
igs_uc_transactions_pkg.insert_row (
x_mode => p_mode, --jchakrab modified for 4380412
x_rowid => lv_rowid,
x_uc_tran_id => l_uc_tran_id,
x_transaction_id => NULL,
x_datetimestamp => NULL,
x_updater => NULL,
x_error_code => NULL,
x_transaction_type => P_Tran_type,
x_app_no => P_App_no,
x_choice_no => P_Choice_no,
x_decision => P_Decision,
x_program_code => P_Course,
x_campus => P_Campus,
x_entry_month => P_Entry_month,
x_entry_year => P_Entry_year,
x_entry_point => P_Entry_point,
x_soc => l_soc,
x_comments_in_offer => l_comments_in_offer,
x_return1 => l_return1,
x_return2 => l_return2,
x_hold_flag => l_hold,
x_sent_to_ucas => 'N',
x_test_cond_cat => P_cond_cat,
x_test_cond_name => P_cond_name,
x_inst_reference => P_Inst_reference ,
x_auto_generated_flag => P_auto_generated,
x_system_code => p_system_code,
x_ucas_cycle => p_ucas_cycle,
x_modular => p_modular,
x_part_time => p_part_time);
igs_uc_transactions_pkg.update_row(
x_rowid => l_transactions.rowid,
x_uc_tran_id => l_transactions.uc_tran_id,
x_transaction_id => l_transactions.transaction_id,
x_datetimestamp => l_transactions.datetimestamp,
x_updater => l_transactions.updater,
x_error_code => l_transactions.error_code,
x_transaction_type => l_transactions.transaction_type,
x_app_no => l_transactions.app_no,
x_choice_no => l_transactions.choice_no,
x_decision => l_transactions.decision,
x_program_code => l_transactions.program_code,
x_campus => l_transactions.campus,
x_entry_month => l_transactions.entry_month,
x_entry_year => l_transactions.entry_year,
x_entry_point => l_transactions.entry_point,
x_soc => l_transactions.soc ,
x_comments_in_offer => l_transactions.comments_in_offer,
x_return1 => l_transactions.return1,
x_return2 => l_transactions.return2,
x_hold_flag => 'Y',
x_sent_to_ucas => l_transactions.sent_to_ucas,
x_test_cond_cat => l_transactions.test_cond_cat,
x_test_cond_name => l_transactions.test_cond_name ,
x_mode => p_mode, --jchakrab modified for 4380412
x_inst_reference => l_transactions.inst_reference,
x_auto_generated_flag => l_transactions.auto_generated_flag,
x_system_code => l_transactions.system_code,
x_ucas_cycle => l_transactions.ucas_cycle,
x_modular => l_transactions.modular,
x_part_time => l_transactions.part_time);
igs_uc_transactions_pkg.insert_row (
x_mode => p_mode, --jchakrab modified for 4380412
x_rowid => lv_rowid,
x_uc_tran_id => l_uc_tran_id,
x_transaction_id => NULL,
x_datetimestamp => NULL,
x_updater => NULL,
x_error_code => NULL,
x_transaction_type => P_Tran_type,
x_app_no => P_App_no,
x_choice_no => P_Choice_no,
x_decision => NULL,
x_program_code => P_Course,
x_campus => P_Campus,
x_entry_month => NULL,
x_entry_year => NULL,
x_entry_point => NULL,
x_soc => NULL,
x_comments_in_offer => P_Free_Format,
x_return1 => l_return1,
x_return2 => l_return2,
x_hold_flag => l_hold,
x_sent_to_ucas => 'N',
x_test_cond_cat => P_cond_cat,
x_test_cond_name => P_cond_name,
x_inst_reference => P_Inst_reference,
x_auto_generated_flag => P_auto_generated,
x_system_code => p_system_code,
x_ucas_cycle => p_ucas_cycle,
x_modular => p_modular,
x_part_time => p_part_time);
SELECT marvin_code, summ_of_cond FROM igs_uc_offer_conds
WHERE condition_category = cp_condition_category
AND condition_name = cp_condition_name;
Also updates igs_uc_transactions with info. from TRANIN and update the
igs_uc_offer_conds table with status
Know limitations, enhancements or remarks
Change History
Who When What
rbezawad 4-Apr-2002 1) Inistitution Reference Column is added w.r.t. UCCR002 Data Model Change.
2) Exception Handling part is modifiled
rbezawad 2-May-2002 Modified w.r.t. UCCR003 Build.Bug No: 2311662
In trans_write() procedure added validation for Checking Interface Type and perform validations accordingly.
rbezawad 11-Jul-2002 Modified code in the exception handling part to raise the error when the exception occurs.
Also while inserting records into traning, the columns TRANSACTIONID,TIMESTAMP,UPDATER,ERRORCODE values are passed as NULL.
These modifications are done as part of 2450438.
rbezawad 16-Jul-2002 While inserting Transactions into Tranin, for the program_code and Campus fields, logic is added to replace the '*' character with ' '.
Added logic for better reporting while exporting transactions. Modifications are done w.r.t. Bug 2462096
rbezawad 16-Jul-2002 Added logic to populate the columns SUMM_OF_COND, LETTER_TEXT columns of table IGS_UC_OFFER_CONDS w.r.t. Bug 2461913.
smaddali 02-oct-2002 Modified for bug 2603384
1) added new column auto_generated_flag to tbh calls to igs_uc_transactions_pkg
2) modified cursor cur_records_to_write to fetch column auto_generated_flag
3) modified igs_uc_transactions.update_row call to update test_cond_cat and name
fields with proper values rather than with NULL
ayedubat 05-DEC-2002 Changed the Log file generations of transactions being processed in the current run for bug: 2462078
Replaced the message, IGS_UC_TRANS_PROC_LOG with the new message 'IGS_UC_TRANS_LOG_HEADER' for dsiplaying
the header line for the transaction records.
ayedubat 21-Mar-03 Removed local variable l_log_text w.r.t. Bug 2841582.
pmarada 09-Jun-03 created this procedure Proc_Tranin_2003 and added cycle in required places. as per UCFD203 Multiple cycles
arvsrini 26-APR-04 Added code to update IGS_UC_TRANSACTIONS.SOC based on errorcode value Bug#3576288
(reverse chronological order - newest change first)
***************************************************************/
CURSOR cur_records_to_write(cp_conf_cycle igs_uc_defaults.configured_cycle%TYPE) IS
SELECT a.ROWID,a.uc_tran_id,a.transaction_id, a.datetimestamp,
a.updater, a.error_code, a.transaction_type, a.app_no,
a.choice_no, a.decision, a.program_code,
a.campus, a.entry_month, a.entry_year, a.entry_point,
a.soc, a.comments_in_offer, a.return1, a.return2,
a.hold_flag, a.created_by, a.creation_date,
a.last_updated_by, a.last_update_date, a.last_update_login,
a.sent_to_ucas, a.test_cond_cat, a.test_cond_name, a.inst_reference ,
a.auto_generated_flag, a.system_code , a.ucas_cycle, a.modular, a.part_time
FROM igs_uc_transactions a, igs_uc_cyc_defaults b
WHERE a.sent_to_ucas = 'N' AND a.hold_flag = 'N'
AND a.ucas_cycle = cp_conf_cycle
AND a.system_code = b.system_code
AND a.ucas_cycle = b.ucas_cycle
AND b.ucas_interface = 'H'
ORDER BY a.creation_date;
CURSOR cur_update_info(cp_appno igs_uc_transactions.app_no%TYPE) IS
SELECT appno, choiceno, transactionID, errorcode,
timestamp, updater, return1, return2, soc
FROM igs_uc_u_tranin_2003
WHERE appno = cp_appno
AND transactionID = (SELECT MAX(TransactionID)
FROM igs_uc_u_tranin_2003
WHERE appno = cp_appno);
SELECT a.ROWID,a.condition_category, a.condition_name,
a.effective_from, a.effective_to, a.status, a.marvin_code,
a.summ_of_cond, a.letter_text, a.created_by,
a.creation_date, a.last_updated_by,
a.last_update_date, a.last_update_login, a.decision
FROM igs_uc_offer_conds a
WHERE a.condition_category = cp_test_cond_cat
AND a.condition_name = cp_test_cond_name;
INSERT INTO igs_uc_u_tranin_2003(transactionid,
timestamp,
updater,
errorcode,
transactiontype,
appno,
choiceno,
decision,
course,
campus,
entrymonth,
entryyear,
entrypoint,
SOC,
freeformat,
return1,
return2,
instreference
)
VALUES(NULL,
NULL,
NULL,
NULL,
uc_transaction_rec.transaction_type,
uc_transaction_rec.app_no,
uc_transaction_rec.choice_no,
uc_transaction_rec.decision,
REPLACE(uc_transaction_rec.program_code, '*', ' '),
REPLACE(uc_transaction_rec.campus, '*', ' '),
uc_transaction_rec.entry_month,
uc_transaction_rec.entry_year,
uc_transaction_rec.entry_point,
uc_transaction_rec.SOC,
uc_transaction_rec.comments_in_offer,
uc_transaction_rec.return1,
uc_transaction_rec.return2,
uc_transaction_rec.inst_reference
);
FOR tranin_rec IN cur_update_info(l_app_no)
LOOP
IF tranin_rec.errorcode = 0 THEN --to update the SOC based on the errorcode value
l_soc:=tranin_rec.soc;
igs_uc_transactions_pkg.update_row (
x_mode => 'R',
x_rowid => uc_transaction_rec.ROWID,
x_uc_tran_id => uc_transaction_rec.UC_Tran_Id,
x_transaction_id => tranin_rec.transactionid,
x_datetimestamp => tranin_rec.timestamp,
x_updater => tranin_rec.updater,
x_error_code => tranin_rec.errorcode,
x_transaction_type => uc_transaction_rec.transaction_type,
x_app_no => uc_transaction_rec.app_no,
x_choice_no => uc_transaction_rec.choice_no,
x_decision => uc_transaction_rec.decision,
x_program_code => uc_transaction_rec.program_code,
x_campus => uc_transaction_rec.campus,
x_entry_month => uc_transaction_rec.entry_month,
x_entry_year => uc_transaction_rec.entry_year,
x_entry_point => uc_transaction_rec.entry_point,
x_soc => l_soc,
x_comments_in_offer => uc_transaction_rec.comments_in_offer,
x_return1 => tranin_rec.return1,
x_return2 => tranin_rec.return2,
x_hold_flag => uc_transaction_rec.hold_flag,
x_sent_to_ucas => 'Y',
x_test_cond_cat => uc_transaction_rec.test_cond_cat,
x_test_cond_name => uc_transaction_rec.test_cond_name,
x_inst_reference => uc_transaction_rec.inst_reference ,
x_auto_generated_flag => uc_transaction_rec.auto_generated_flag,
x_system_code => uc_transaction_rec.system_code,
x_ucas_cycle => uc_transaction_rec.ucas_cycle,
x_modular => uc_transaction_rec.modular,
x_part_time => uc_transaction_rec.part_time);
igs_uc_offer_conds_pkg.update_row (
x_mode => 'R',
x_rowid => offer_conds_rec1.ROWID,
x_condition_category => offer_conds_rec1.condition_category,
x_condition_name => offer_conds_rec1.condition_name,
x_effective_from => offer_conds_rec1.effective_from,
x_effective_to => offer_conds_rec1.effective_to,
x_status => 'A',
x_marvin_code => offer_conds_rec1.marvin_code,
x_summ_of_cond => NVL(offer_conds_rec1.summ_of_cond, tranin_rec.soc),
x_letter_text => tranin_rec.return2,
x_decision => offer_conds_rec1.decision
);
igs_uc_offer_conds_pkg.update_row (
x_mode => 'R',
x_rowid => offer_conds_rec1.ROWID,
x_condition_category => offer_conds_rec1.condition_category,
x_condition_name => offer_conds_rec1.condition_name,
x_effective_from => offer_conds_rec1.effective_from,
x_effective_to => offer_conds_rec1.effective_to,
x_status => 'F',
x_marvin_code => offer_conds_rec1.marvin_code,
x_summ_of_cond => NVL(offer_conds_rec1.summ_of_cond, tranin_rec.soc),
x_letter_text => tranin_rec.return2,
x_decision => offer_conds_rec1.decision
);
Also updates igs_uc_transactions with info. from TRANIN, and update the
igs_uc_offer_conds table with status.
Know limitations, enhancements or remarks
Change History (reverse chronological order - newest change first)
Who When What
jchakrab 23-May-2006 Modified for 5155053 - use roundno column (for choice no) for GTTR transactions
jbaber 19-Aug-2005 Modified for UC307 - HERCULES Small Systems Support
Added p_system_code parameter
jbaber 12-Jul-2004 Modified for UC315 - UCAS 2006 Support
Appended check_digit to appno when configured cycle > 2005
arvsrini 26-Apr-2004 Added code to update IGS_UC_TRANSACTIONS.SOC based on
the errorcode value Bug#3576288
**********************************************************************/
CURSOR cur_records_to_write(cp_conf_cycle igs_uc_defaults.configured_cycle%TYPE) IS
SELECT a.ROWID,a.uc_tran_id,a.transaction_id, a.datetimestamp,
a.updater, a.error_code, a.transaction_type, a.app_no,
a.choice_no, a.decision, a.program_code,
a.campus, a.entry_month, a.entry_year, a.entry_point,
a.soc, a.comments_in_offer, a.return1, a.return2,
a.hold_flag, a.created_by, a.creation_date,
a.last_updated_by, a.last_update_date, a.last_update_login,
a.sent_to_ucas, a.test_cond_cat, a.test_cond_name, a.inst_reference ,
a.auto_generated_flag, a.system_code , a.ucas_cycle, a.modular, a.part_time
FROM igs_uc_transactions a, igs_uc_cyc_defaults b
WHERE a.sent_to_ucas = 'N' AND a.hold_flag = 'N'
AND a.ucas_cycle = cp_conf_cycle
AND a.system_code = p_system_code
AND a.system_code = b.system_code
AND a.ucas_cycle = b.ucas_cycle
AND b.ucas_interface = 'H'
ORDER BY a.creation_date;
CURSOR cur_update_info(cp_appno igs_uc_transactions.app_no%TYPE) IS
SELECT appno, choiceno, transactionID, errorcode,
timestamp, updater, return1, return2, soc, roundno
FROM igs_uc_u_tranin_2004
WHERE appno = cp_appno
AND transactionID = (SELECT MAX(TransactionID)
FROM igs_uc_u_tranin_2004
WHERE appno = cp_appno);
SELECT a.ROWID,a.condition_category, a.condition_name,
a.effective_from, a.effective_to, a.status, a.marvin_code,
a.summ_of_cond, a.letter_text, a.created_by,
a.creation_date, a.last_updated_by,
a.last_update_date, a.last_update_login, a.decision
FROM igs_uc_offer_conds a
WHERE a.condition_category = cp_test_cond_cat
AND a.condition_name = cp_test_cond_name;
SELECT TO_NUMBER(app_no || check_digit)
FROM igs_uc_applicants
WHERE app_no = cp_appno;
INSERT INTO igs_uc_u_tranin_2004(transactionid,
timestamp,
updater,
errorcode,
transactiontype,
appno,
choiceno,
decision,
course,
campus,
entrymonth,
entryyear,
entrypoint,
SOC,
freeformat,
return1,
return2,
instreference,
syscode,
roundno,
modular,
parttime)
VALUES(NULL,
NULL,
NULL,
NULL,
uc_transaction_rec.transaction_type,
l_appno,
NULL,
uc_transaction_rec.decision,
REPLACE(uc_transaction_rec.program_code, '*', ' '),
REPLACE(uc_transaction_rec.campus, '*', ' '),
uc_transaction_rec.entry_month,
uc_transaction_rec.entry_year,
uc_transaction_rec.entry_point,
uc_transaction_rec.SOC,
uc_transaction_rec.comments_in_offer,
uc_transaction_rec.return1,
uc_transaction_rec.return2,
uc_transaction_rec.inst_reference,
uc_transaction_rec.system_code,
uc_transaction_rec.choice_no,
uc_transaction_rec.modular,
uc_transaction_rec.part_time);
INSERT INTO igs_uc_u_tranin_2004(transactionid,
timestamp,
updater,
errorcode,
transactiontype,
appno,
choiceno,
decision,
course,
campus,
entrymonth,
entryyear,
entrypoint,
SOC,
freeformat,
return1,
return2,
instreference,
syscode,
roundno,
modular,
parttime)
VALUES(NULL,
NULL,
NULL,
NULL,
uc_transaction_rec.transaction_type,
l_appno,
uc_transaction_rec.choice_no,
uc_transaction_rec.decision,
REPLACE(uc_transaction_rec.program_code, '*', ' '),
REPLACE(uc_transaction_rec.campus, '*', ' '),
uc_transaction_rec.entry_month,
uc_transaction_rec.entry_year,
uc_transaction_rec.entry_point,
uc_transaction_rec.SOC,
uc_transaction_rec.comments_in_offer,
uc_transaction_rec.return1,
uc_transaction_rec.return2,
uc_transaction_rec.inst_reference,
uc_transaction_rec.system_code,
NULL,
NULL,
NULL);
FOR tranin_rec IN cur_update_info(l_appno)
LOOP
IF tranin_rec.errorcode = 0 THEN --to update the SOC based on the errorcode value
l_soc:=tranin_rec.soc;
igs_uc_transactions_pkg.update_row (
x_mode => 'R',
x_rowid => uc_transaction_rec.ROWID,
x_uc_tran_id => uc_transaction_rec.UC_Tran_Id,
x_transaction_id => tranin_rec.transactionid,
x_datetimestamp => tranin_rec.timestamp,
x_updater => tranin_rec.updater,
x_error_code => tranin_rec.errorcode,
x_transaction_type => uc_transaction_rec.transaction_type,
x_app_no => uc_transaction_rec.app_no,
x_choice_no => uc_transaction_rec.choice_no,
x_decision => uc_transaction_rec.decision,
x_program_code => uc_transaction_rec.program_code,
x_campus => uc_transaction_rec.campus,
x_entry_month => uc_transaction_rec.entry_month,
x_entry_year => uc_transaction_rec.entry_year,
x_entry_point => uc_transaction_rec.entry_point,
x_soc => l_soc,
x_comments_in_offer => uc_transaction_rec.comments_in_offer,
x_return1 => tranin_rec.return1,
x_return2 => tranin_rec.return2,
x_hold_flag => uc_transaction_rec.hold_flag,
x_sent_to_ucas => 'Y',
x_test_cond_cat => uc_transaction_rec.test_cond_cat,
x_test_cond_name => uc_transaction_rec.test_cond_name,
x_inst_reference => uc_transaction_rec.inst_reference ,
x_auto_generated_flag => uc_transaction_rec.auto_generated_flag,
x_system_code => uc_transaction_rec.system_code,
x_ucas_cycle => uc_transaction_rec.ucas_cycle,
x_modular => uc_transaction_rec.modular,
x_part_time => uc_transaction_rec.part_time);
igs_uc_offer_conds_pkg.update_row (
x_mode => 'R',
x_rowid => offer_conds_rec1.ROWID,
x_condition_category => offer_conds_rec1.condition_category,
x_condition_name => offer_conds_rec1.condition_name,
x_effective_from => offer_conds_rec1.effective_from,
x_effective_to => offer_conds_rec1.effective_to,
x_status => 'A',
x_marvin_code => offer_conds_rec1.marvin_code,
x_summ_of_cond => NVL(offer_conds_rec1.summ_of_cond, tranin_rec.soc),
x_letter_text => tranin_rec.return2,
x_decision => offer_conds_rec1.decision
);
igs_uc_offer_conds_pkg.update_row (
x_mode => 'R',
x_rowid => offer_conds_rec1.ROWID,
x_condition_category => offer_conds_rec1.condition_category,
x_condition_name => offer_conds_rec1.condition_name,
x_effective_from => offer_conds_rec1.effective_from,
x_effective_to => offer_conds_rec1.effective_to,
x_status => 'F',
x_marvin_code => offer_conds_rec1.marvin_code,
x_summ_of_cond => NVL(offer_conds_rec1.summ_of_cond, tranin_rec.soc),
x_letter_text => tranin_rec.return2,
x_decision => offer_conds_rec1.decision
);
SELECT max(current_cycle) current_cycle, max(configured_cycle) configured_cycle
FROM igs_uc_defaults ;
which the Admission Application instance has to be updated with, after validating
the Old and New Outcome statuses and Offer Response Status against UCAS setup.
Know limitations, enhancements or remarks
Change History
*************************************************************/
-- Cursor to find the system code associated with the UCAS application
CURSOR c_sys_cd (cp_alt_appl_id igs_ad_appl_all.alt_appl_id%TYPE ) IS
SELECT system_code
FROM igs_uc_applicants
WHERE TO_CHAR(app_no) = cp_alt_appl_id;
SELECT decision_code
FROM igs_uc_map_out_stat
WHERE system_code = cp_system_code
AND adm_outcome_status = cp_outcome_status
AND closed_ind = 'N';
SELECT 'X'
FROM igs_uc_map_off_resp
WHERE decision_code= cp_decision_code
AND system_code = cp_system_code
AND closed_ind = 'N'
AND reply_code = 'F'
AND adm_offer_resp_status = cp_adm_offer_resp_status;
SELECT adm_offer_resp_status
FROM igs_uc_map_off_resp
WHERE system_code = cp_system_code
AND decision_code= cp_decision_code
AND closed_ind = 'N'
AND reply_code = 'F' ;