[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
a empty record is inserted into IGS_UC_UCAS_CONTROL to be updated later by user
anwest 18-JAN-2006 Bug# 4950285 R12 Disable OSS Mandate
***************************************************************/
-- Cursor to check if any records exist in IGS_UC_DEFAULTS
CURSOR cur_uc_defaults IS
SELECT 'X'
FROM igs_uc_defaults;
SELECT MAX(current_cycle) current_cycle, MAX(configured_cycle) configured_cycle
FROM igs_uc_defaults;
SELECT 'X' FROM igs_uc_defaults
WHERE (current_cycle IS NULL OR configured_cycle IS NULL)
AND system_code <> 'S';
SELECT MAX(TO_NUMBER(lookup_code)) - 1
FROM igs_lookup_values
WHERE lookup_type = 'IGS_UC_SUP_CYCLE'
AND enabled_flag = 'Y'
AND NVL(closed_Ind, 'N') = 'N';
SELECT TO_NUMBER(lookup_code)
FROM igs_lookup_values
WHERE lookup_type = 'IGS_UC_SUP_CYCLE'
AND TO_NUMBER(lookup_code) = p_current_cycle + 1
AND enabled_flag = 'Y'
AND NVL(closed_Ind, 'N') = 'N';
SELECT ucd.*
FROM igs_uc_cyc_defaults ucd
WHERE ucas_cycle = p_current_cycle
AND ucd.system_code <> 'S';
SELECT 'X'
FROM igs_uc_cyc_defaults ucd
WHERE system_code = p_system_code
AND ucas_cycle = p_ucas_cycle;
SELECT uco.rowid, uco.*
FROM igs_uc_ucas_control uco
WHERE uco.system_code <> 'S'
AND ucas_cycle = p_current_cycle;
SELECT 'X'
FROM igs_uc_ucas_control ucd
WHERE system_code = p_system_code
AND ucas_cycle = p_ucas_cycle;
SELECT ucd.rowid, ucd.*
FROM igs_uc_defaults ucd
WHERE ucd.system_code <> 'S';
igs_uc_defaults_pkg.update_row ( x_rowid => rec_cur_defaults.rowid,
x_current_inst_code => rec_cur_defaults.current_inst_code,
x_ucas_id_format => rec_cur_defaults.ucas_id_format,
x_test_app_no => rec_cur_defaults.test_app_no,
x_test_choice_no => rec_cur_defaults.test_choice_no,
x_test_transaction_type => rec_cur_defaults.test_transaction_type,
x_copy_ucas_id => rec_cur_defaults.copy_ucas_id,
x_mode => 'R',
x_decision_make_id => rec_cur_defaults.decision_make_id,
x_decision_reason_id => rec_cur_defaults.decision_reason_id,
x_obsolete_outcome_status => rec_cur_defaults.obsolete_outcome_status,
x_pending_outcome_status => rec_cur_defaults.pending_outcome_status,
x_rejected_outcome_status => rec_cur_defaults.rejected_outcome_status,
x_system_code => rec_cur_defaults.system_code,
x_ni_number_alt_pers_type => rec_cur_defaults.ni_number_alt_pers_type,
x_application_type => rec_cur_defaults.application_type,
x_name => rec_cur_defaults.name,
x_description => rec_cur_defaults.description,
x_ucas_security_key => rec_cur_defaults.ucas_security_key,
x_current_cycle => l_curr_cycle,
x_configured_cycle => l_conf_cycle,
x_prev_inst_left_date => rec_cur_defaults.prev_inst_left_date
);
igs_uc_ucas_control_pkg.insert_row( x_rowid => l_rowid,
x_entry_year => Ltrim(Substr(l_curr_cycle,3,2)),
x_time_of_year => NULL,
x_time_of_day => NULL,
x_routeb_time_of_year => NULL,
x_appno_first => l_appno_first, -- put value to expected appno ranges as value cannot be derived at this stage
x_appno_maximum => l_appno_maximum, -- put value to expected appno ranges as value cannot be derived at this stage
x_appno_last_used => NULL,
x_last_daily_run_no => NULL,
x_last_daily_run_date => NULL,
x_appno_15dec => NULL,
x_run_date_15dec => NULL,
x_appno_24mar => NULL,
x_run_date_24mar => NULL,
x_appno_16may => NULL,
x_run_date_16may => NULL,
x_appno_decision_proc => NULL,
x_run_date_decision_proc => NULL,
x_appno_first_pre_num => NULL,
x_news => NULL,
x_no_more_la_tran => NULL,
x_star_x_avail => NULL,
x_mode => 'R',
x_appno_first_opf => 0,
x_appno_first_rpa_noneu => 0,
x_appno_first_rpa_eu => 0,
x_extra_start_date => NULL,
x_last_passport_date => NULL,
x_last_le_date => NULL,
x_system_code => rec_cur_defaults.system_code,
x_ucas_cycle => l_curr_cycle,
x_gttr_clear_toy_code => NULL,
x_transaction_toy_code => 'S'
);
igs_uc_cyc_defaults_pkg.insert_row( x_rowid => l_rowid,
x_SYSTEM_CODE => rec_uc_cyc_defaults.system_code,
x_UCAS_CYCLE => l_next_cycle,
x_UCAS_INTERFACE => rec_uc_cyc_defaults.ucas_interface,
x_MARVIN_SEQ => 0,
x_CLEARING_FLAG => rec_uc_cyc_defaults.clearing_flag,
x_EXTRA_FLAG => rec_uc_cyc_defaults.extra_flag,
x_CVNAME_FLAG => rec_uc_cyc_defaults.cvname_flag,
x_MODE => 'R'
);
igs_uc_ucas_control_pkg.insert_row( x_rowid => l_rowid,
x_entry_year => rec_cur_uc_ucas_control.entry_year + 1,
x_time_of_year => NULL,
x_time_of_day => NULL,
x_routeb_time_of_year => NULL,
x_appno_first => rec_cur_uc_ucas_control.appno_first + 1000000,
x_appno_maximum => rec_cur_uc_ucas_control.appno_maximum + 1000000,
x_appno_last_used => NULL,
x_last_daily_run_no => NULL,
x_last_daily_run_date => NULL,
x_appno_15dec => NULL,
x_run_date_15dec => NULL,
x_appno_24mar => NULL,
x_run_date_24mar => NULL,
x_appno_16may => NULL,
x_run_date_16may => NULL,
x_appno_decision_proc => NULL,
x_run_date_decision_proc => NULL,
x_appno_first_pre_num => NULL,
x_news => NULL,
x_no_more_la_tran => NULL,
x_star_x_avail => NULL,
x_mode => 'R',
x_appno_first_opf => 0,
x_appno_first_rpa_noneu => 0,
x_appno_first_rpa_eu => 0,
x_extra_start_date => NULL,
x_last_passport_date => NULL,
x_last_le_date => NULL,
x_system_code => rec_cur_uc_ucas_control.system_code,
x_ucas_cycle => l_next_cycle,
x_gttr_clear_toy_code => NULL,
x_transaction_toy_code => 'S'
);
igs_uc_defaults_pkg.update_row ( x_rowid => rec_cur_defaults.rowid,
x_current_inst_code => rec_cur_defaults.current_inst_code,
x_ucas_id_format => rec_cur_defaults.ucas_id_format,
x_test_app_no => rec_cur_defaults.test_app_no,
x_test_choice_no => rec_cur_defaults.test_choice_no,
x_test_transaction_type => rec_cur_defaults.test_transaction_type,
x_copy_ucas_id => rec_cur_defaults.copy_ucas_id,
x_mode => 'R',
x_decision_make_id => rec_cur_defaults.decision_make_id,
x_decision_reason_id => rec_cur_defaults.decision_reason_id,
x_obsolete_outcome_status => rec_cur_defaults.obsolete_outcome_status,
x_pending_outcome_status => rec_cur_defaults.pending_outcome_status,
x_rejected_outcome_status => rec_cur_defaults.rejected_outcome_status,
x_system_code => rec_cur_defaults.system_code,
x_ni_number_alt_pers_type => rec_cur_defaults.ni_number_alt_pers_type,
x_application_type => rec_cur_defaults.application_type,
x_name => rec_cur_defaults.name,
x_description => rec_cur_defaults.description,
x_ucas_security_key => rec_cur_defaults.ucas_security_key,
x_current_cycle => l_next_cycle,
x_configured_cycle => rec_cur_defaults.configured_cycle,
x_prev_inst_left_date => rec_cur_defaults.prev_inst_left_date
);