[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT LTRIM(RTRIM(value))
FROM V$PARAMETER
WHERE name ='utl_file_dir';
SELECT DECODE( INSTR(l_dbvalue,',',l_start_str_index),0,LENGTH(l_dbvalue)+1, INSTR(l_dbvalue,',',l_start_str_index) )
FROM DUAL ;
SELECT DISTINCT current_inst_code
FROM igs_uc_defaults ;
|| As part of this change, all the transactions are selecting
|| from IGS_UC_TRANSACTIONS table instead of IGS_UC_MV_TRANIN.
|| Created the new 'LE' type of transaction.
|| Enh bug # 2643048
|| ayedubat 13-DEC-2002 Changed the LA and LD transactions to write the Course element,
|| Entry Year and Entry month , Point of Entry, Sumary of Conditions
|| and Comments in Offer elements, for bug# 2711416
|| ayedubat 02-JUL-2003 Changed as part of Multiple Cycles Enhancement, 2669208
|| Replaced the tables igs_uc_mv_uvcrs_vacm, igs_uc_mv_uvcrs_vop and
|| igs_uc_mv_uvofr_abv with igs_uc_crse_dets, igs_uc_crse_vac_ops and
|| igs_uc_ref_off_abrv to fecth the Transactions.
||
|| (reverse chronological order - newest change first)
*/
-- Cursor to fetch the pending transactions of a given UCAS System
CURSOR cur_transactions( p_system_code IGS_UC_TRANSACTIONS.system_code%TYPE ) IS
SELECT
ROWID,
uc_tran_id,
transaction_id,
datetimestamp,
updater,
error_code,
transaction_type,
app_no,
choice_no,
decision,
program_code,
campus,
entry_month,
entry_year,
entry_point,
soc,
comments_in_offer,
return1,
return2,
hold_flag,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
sent_to_ucas,
test_cond_cat,
test_cond_name,
inst_reference ,
auto_generated_flag,
system_code,
ucas_cycle,
modular,
part_time
FROM IGS_UC_TRANSACTIONS
WHERE system_code = p_system_code
AND ucas_cycle = p_configured_cycle
AND sent_to_ucas = 'N'
AND hold_flag = 'N'
AND transaction_type NOT IN ('XA','XD')
ORDER BY transaction_type;
SELECT TO_CHAR(TO_DATE(p_mon,'MM'),'MON') entry_month
FROM DUAL;
SELECT
crse.ROWID,
REPLACE(crse.ucas_program_code,'*',' ') course,
REPLACE(crse.ucas_campus, '*', ' ') campus,
crse.vacancy_status,
crse.no_of_vacancy,
crse.score,
crse.rb_full,
crse.scot_vac
FROM igs_uc_crse_dets crse
WHERE crse.system_code = cp_system_code AND
crse.institute = p_current_inst_code AND
crse.sent_to_ucas = 'N' AND
NVL(crse.imported,'Y') = 'Y'
ORDER BY crse.ucas_program_code , crse.ucas_campus;
SELECT
crse.ROWID,
crse.*
FROM igs_uc_crse_dets crse
WHERE crse.ROWID = cp_rowid;
SELECT
crsevac.ROWID,
REPLACE(crsevac.ucas_program_code, '*', ' ') course,
REPLACE(crsevac.ucas_campus, '*', ' ') campus,
REPLACE(crsevac.option_code, '*', ' ') option_code
FROM igs_uc_crse_vac_ops crsevac
WHERE crsevac.system_code = cp_system_code AND
REPLACE(crsevac.ucas_program_code,'*',' ') = cp_course AND
REPLACE(crsevac.ucas_campus, '*', ' ') = cp_campus AND
crsevac.institute = p_current_inst_code AND
crsevac.sent_to_ucas = 'N'
ORDER BY crsevac.ucas_program_code , crsevac.ucas_campus , crsevac.option_code;
SELECT
crsevac.ROWID,
crsevac.*
FROM igs_uc_crse_vac_ops crsevac
WHERE crsevac.ROWID = cp_rowid;
SELECT
roa.ROWID,
REPLACE(roa.abbrev_code,'*', ' ') abbrev_code,
roa.abbrev_text ,
REPLACE(roa.letter_format, '*', ' ') letter_format,
REPLACE(roa.summary_char, '*', ' ') summary_char,
roa.deleted
FROM igs_uc_ref_off_abrv roa
WHERE roa.sent_to_ucas = 'N' AND roa.deleted = 'N';
SELECT
roa.ROWID,
roa.*
FROM igs_uc_ref_off_abrv roa
WHERE roa.ROWID = cp_rowid;
igs_uc_transactions_pkg.update_row (
x_mode => 'R',
x_rowid => cur_transactions_rec.ROWID,
x_uc_tran_id => cur_transactions_rec.UC_Tran_Id,
x_transaction_id => cur_transactions_rec.transaction_id,
x_datetimestamp => cur_transactions_rec.datetimestamp,
x_updater => cur_transactions_rec.updater,
x_error_code => cur_transactions_rec.error_code,
x_transaction_type => cur_transactions_rec.transaction_type,
x_app_no => cur_transactions_rec.app_no,
x_choice_no => cur_transactions_rec.choice_no,
x_decision => cur_transactions_rec.decision,
x_program_code => cur_transactions_rec.program_code,
x_campus => cur_transactions_rec.campus,
x_entry_month => cur_transactions_rec.entry_month,
x_entry_year => cur_transactions_rec.entry_year,
x_entry_point => cur_transactions_rec.entry_point,
x_soc => cur_transactions_rec.SOC,
x_comments_in_offer => cur_transactions_rec.comments_in_offer,
x_return1 => cur_transactions_rec.return1,
x_return2 => cur_transactions_rec.return2,
x_hold_flag => cur_transactions_rec.hold_flag,
x_sent_to_ucas => 'Y',
x_test_cond_cat => cur_transactions_rec.test_cond_cat,
x_test_cond_name => cur_transactions_rec.test_cond_name,
x_inst_reference => cur_transactions_rec.inst_reference ,
x_auto_generated_flag => cur_transactions_rec.auto_generated_flag,
x_system_code => cur_transactions_rec.system_code,
x_ucas_cycle => cur_transactions_rec.ucas_cycle,
x_modular => cur_transactions_rec.modular,
x_part_time => cur_transactions_rec.part_time);
igs_uc_crse_vac_ops_pkg.update_row (
x_mode => 'R',
x_rowid => upd_crs_vac_opt_rec.rowid,
x_ucas_program_code => upd_crs_vac_opt_rec.ucas_program_code,
x_institute => upd_crs_vac_opt_rec.institute,
x_ucas_campus => upd_crs_vac_opt_rec.ucas_campus,
x_option_code => upd_crs_vac_opt_rec.option_code,
x_updater => upd_crs_vac_opt_rec.updater,
x_cl_updated => upd_crs_vac_opt_rec.cl_updated,
x_cl_date => upd_crs_vac_opt_rec.cl_date,
x_vacancy_status => upd_crs_vac_opt_rec.vacancy_status,
x_sent_to_ucas => 'Y' ,
x_system_code => upd_crs_vac_opt_rec.system_code );
igs_uc_crse_dets_pkg.update_row (
x_mode => 'R',
x_rowid => upd_crs_vac_rec.rowid,
x_ucas_program_code => upd_crs_vac_rec.ucas_program_code,
x_oss_program_code => upd_crs_vac_rec.oss_program_code,
x_oss_program_version => upd_crs_vac_rec.oss_program_version,
x_institute => upd_crs_vac_rec.institute,
x_uvcourse_updater => upd_crs_vac_rec.uvcourse_updater,
x_uvcrsevac_updater => upd_crs_vac_rec.uvcrsevac_updater,
x_short_title => upd_crs_vac_rec.short_title,
x_long_title => upd_crs_vac_rec.long_title,
x_ucas_campus => upd_crs_vac_rec.ucas_campus,
x_oss_location => upd_crs_vac_rec.oss_location,
x_faculty => upd_crs_vac_rec.faculty,
x_total_no_of_seats => upd_crs_vac_rec.total_no_of_seats,
x_min_entry_points => upd_crs_vac_rec.min_entry_points,
x_max_entry_points => upd_crs_vac_rec.max_entry_points,
x_current_validity => upd_crs_vac_rec.current_validity,
x_deferred_validity => upd_crs_vac_rec.deferred_validity,
x_term_1_start => upd_crs_vac_rec.term_1_start,
x_term_1_end => upd_crs_vac_rec.term_1_end,
x_term_2_start => upd_crs_vac_rec.term_2_start,
x_term_2_end => upd_crs_vac_rec.term_2_end,
x_term_3_start => upd_crs_vac_rec.term_3_start,
x_term_3_end => upd_crs_vac_rec.term_3_end,
x_term_4_start => upd_crs_vac_rec.term_4_start,
x_term_4_end => upd_crs_vac_rec.term_4_end,
x_cl_updated => upd_crs_vac_rec.cl_updated,
x_cl_date => upd_crs_vac_rec.cl_date,
x_vacancy_status => upd_crs_vac_rec.vacancy_status,
x_no_of_vacancy => upd_crs_vac_rec.no_of_vacancy,
x_score => upd_crs_vac_rec.score,
x_rb_full => upd_crs_vac_rec.rb_full,
x_scot_vac => upd_crs_vac_rec.scot_vac,
x_sent_to_ucas => 'Y',
x_ucas_system_id => upd_crs_vac_rec.ucas_system_id,
x_oss_attendance_type => upd_crs_vac_rec.oss_attendance_type,
x_oss_attendance_mode => upd_crs_vac_rec.oss_attendance_mode,
x_joint_admission_ind => upd_crs_vac_rec.joint_admission_ind,
x_open_extra_ind => upd_crs_vac_rec.open_extra_ind,
x_clearing_options => upd_crs_vac_rec.clearing_options,
x_imported => NVL(upd_crs_vac_rec.imported,'Y'),
x_system_code => upd_crs_vac_rec.system_code ,
x_keywrds_changed => upd_crs_vac_rec.keywrds_changed);
igs_uc_ref_off_abrv_pkg.update_row (
x_mode => 'R',
x_rowid => upd_offr_abb_rec.rowid,
x_abbrev_code => upd_offr_abb_rec.abbrev_code,
x_uv_updater => upd_offr_abb_rec.uv_updater,
x_abbrev_text => upd_offr_abb_rec.abbrev_text,
x_letter_format => upd_offr_abb_rec.letter_format,
x_summary_char => upd_offr_abb_rec.summary_char,
x_uncond => upd_offr_abb_rec.uncond,
x_withdrawal => upd_offr_abb_rec.withdrawal,
x_release => upd_offr_abb_rec.release,
x_imported => upd_offr_abb_rec.imported,
x_sent_to_ucas => 'Y',
x_deleted => upd_offr_abb_rec.deleted,
x_tariff => upd_offr_abb_rec.tariff );
SELECT 'X'
FROM IGS_UC_TRANSACTIONS
WHERE
system_code = NVL(cp_system_code,system_code) AND
NVL(cp_system_code,system_code) IN (SELECT system_code
FROM igs_uc_cyc_defaults
WHERE ucas_interface = 'M' AND
ucas_cycle = cp_config_cycle) AND
ucas_cycle = cp_config_cycle AND
sent_to_ucas = 'N' AND
hold_flag = 'N' AND
transaction_type NOT IN ('XA','XD');
SELECT 'X'
FROM igs_uc_crse_dets crse
WHERE
((cp_system_code IS NOT NULL AND system_code = cp_system_code) OR
(cp_system_code IS NULL AND system_code IN ('U','S'))) AND
crse.institute = cp_current_inst AND
crse.sent_to_ucas = 'N' AND
NVL(crse.imported,'Y') = 'Y' ;
SELECT 'X'
FROM igs_uc_ref_off_abrv roa
WHERE roa.sent_to_ucas = 'N' AND roa.deleted = 'N';
SELECT MAX(configured_cycle) configured_cycle,
MAX(current_cycle) current_cycle
FROM IGS_UC_DEFAULTS ;
SELECT DISTINCT current_inst_code, ucas_security_key
FROM IGS_UC_DEFAULTS ;
SELECT DISTINCT ucd.system_code system_code,
DECODE(ucd.system_code,'U',1,'G',2,'N',3,'S',4,5)
FROM IGS_UC_DEFAULTS ucd, IGS_UC_CYC_DEFAULTS uccd
WHERE ucd.system_code = uccd.system_code AND
uccd.ucas_cycle = cp_ucas_cycle AND
uccd.ucas_interface = 'M'
ORDER BY 2;