[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Insert Row Procedure
of the package call IGS_CA_INST_PKG
ssawhney 28dec GSCC standards change name of
REL_SUP_CI_SEQ_NUM sequence to end in S1
npalanis 23-NOV-2002 Bug : 2563531
For calendars load , teaching and academic the alternate
code is made unique . In rollover the alternate code of the
previous calendar and the rolled over calendar was retained same
now the alternate code is appended with sequence number
(igs_ca_inst_seq_num_s1) to make it unique
asbala 9-Feb-2004 ENCR039: New column TERM_INSTRUCTION_TIME of table IGS_CA_INST will also be rolled
over when the calendar is rolled over.
nsidnaa 9/21/2004 New function added to rollover retention schedules for a teaching calendar.
skpandey 4-AUG-2005 BUG : 4356272
Added parameteric values for SS_DISPLAYED, ADMIN_FLAG, PLANNING_FLAG, SCHEDULE_FLAG in IGS_CA_INST_PKG.INSERT_ROW procedure to
facilitate the adding of new checkboxes to the IGS_CA_INST_ALL table for enabling load calendars for self-service
**************************************************************************/
l_message_name VARCHAR2(30);
SELECT tpret.ret_percentage, tpret.ret_amount
FROM IGS_FI_TP_RET_SCHD tpret
WHERE teach_cal_type = cp_cal_type AND
teach_ci_sequence_number = cp_seq_num AND
dt_alias = cp_dt_alias AND
dai_sequence_number = cp_dt_alias_seq_num AND
fee_cal_type IS NULL;
IGS_FI_TP_RET_SCHD_PKG.insert_row(x_rowid => lv_rowid,
x_ftci_teach_retention_id => l_ftci_teach_retention_id,
x_teach_cal_type => p_old_ca_type,
x_teach_ci_sequence_number => p_new_ci_seq_num,
x_fee_cal_type => null,
x_fee_ci_sequence_number => null,
x_fee_type => null,
x_dt_alias => p_old_da_alias,
x_dai_sequence_number => p_old_dai_seq_num,
x_ret_percentage => chk_da_used_ret_rec.ret_percentage,
x_ret_amount => chk_da_used_ret_rec.ret_amount,
x_mode => 'R'
);
SELECT *
FROM IGS_CA_DA_INST
WHERE DT_ALIAS = cp_dt_alias AND
CAL_TYPE = cp_cal_type AND
ci_sequence_number = cp_ci_sequence_number;
SELECT *
FROM IGS_CA_INST
WHERE CAL_TYPE = cp_cal_type AND
sequence_number = cp_sequence_number;
SELECT *
FROM IGS_CA_INST
WHERE CAL_TYPE = cp_cal_type AND
start_dt = cp_start_dt AND
end_dt = cp_end_dt;
SELECT *
FROM IGS_CA_STAT
WHERE CAL_STATUS = cp_cal_status;
SELECT *
FROM IGS_GE_S_LOG_ENTRY
WHERE s_log_type = cp_s_log_type
AND creation_dt = cp_creation_dt
AND key = cp_key
AND text = cp_text;
SELECT ROWID
FROM IGS_GE_S_LOG_ENTRY
WHERE s_log_type = gv_log_type
AND creation_dt = gv_log_creation_dt
AND key = gv_log_key
AND text = v_other_detail;
IGS_CA_DA_INST_PAIR_PKG.INSERT_ROW(
X_ROWID => X_ROWID,
X_DT_ALIAS=> v_ins_dt_alias,
X_dai_sequence_number => v_ins_dai_sequence_number,
X_CAL_TYPE =>v_ins_cal_type,
X_ci_sequence_number =>v_ins_ci_sequence_number,
X_related_dt_alias =>v_ins_related_dt_alias,
X_related_dai_sequence_number => v_ins_related_dai_seq_num,
X_related_cal_type => v_ins_related_cal_type,
X_related_ci_sequence_number => v_ins_related_ci_seq_num,
X_MODE => 'R');
IGS_GE_S_LOG_ENTRY_PKG.DELETE_ROW(v_IGS_GE_S_LOG_ENTRY_CUR.ROWID);
IGS_GE_S_LOG_ENTRY_PKG.DELETE_ROW(v_IGS_GE_S_LOG_ENTRY_CUR.ROWID);
SELECT *
FROM IGS_CA_DA_INST_OFCNT
WHERE DT_ALIAS = cp_dt_alias AND
dai_sequence_number = cp_dai_sequence_number AND
CAL_TYPE = cp_cal_type AND
ci_sequence_number = cp_ci_sequence_number AND
offset_dt_alias = cp_offset_dt_alias AND
offset_dai_sequence_number = cp_offset_dai_sequence_number AND
offset_cal_type = cp_offset_cal_type AND
offset_ci_sequence_number = cp_offset_ci_sequence_number;
IGS_CA_DA_INST_OFCNT_PKG.INSERT_ROW(
X_ROWID => X_ROWID,
X_DT_ALIAS => p_new_dt_alias,
X_dai_sequence_number => p_new_dai_sequence_number,
X_CAL_TYPE => p_new_cal_type,
X_ci_sequence_number => p_new_ci_sequence_number,
X_offset_dt_alias => p_new_offset_dt_alias,
X_offset_dai_sequence_number => p_new_offset_dai_seq_number,
X_offset_cal_type => p_new_offset_cal_type,
X_offset_ci_sequence_number => p_new_offset_ci_seq_number,
X_S_DT_OFFSET_CONSTRAINT_TYPE => v_daio_cnstrt_rec.S_DT_OFFSET_CONSTRAINT_TYPE,
X_constraint_condition => v_daio_cnstrt_rec.constraint_condition,
X_constraint_resolution => v_daio_cnstrt_rec.constraint_resolution,
X_MODE => 'R');
SELECT rollover_inclusion_ind
FROM IGS_AD_PECRS_OFOP_DT
WHERE adm_cal_type = p_cal_type AND
adm_ci_sequence_number = p_ci_sequence_number AND
DT_ALIAS = p_dt_alias AND
dai_sequence_number = p_dai_sequence_number;
SELECT *
FROM IGS_CA_DA_INST
WHERE DT_ALIAS = cp_dt_alias AND
CAL_TYPE = cp_cal_type AND
ci_sequence_number = cp_ci_sequence_number;
SELECT *
FROM IGS_CA_INST
WHERE CAL_TYPE = cp_cal_type AND
sequence_number = cp_sequence_number;
SELECT *
FROM IGS_CA_INST
WHERE CAL_TYPE = cp_cal_type AND
start_dt = cp_start_dt AND
end_dt = cp_end_dt;
SELECT *
FROM IGS_CA_STAT
WHERE CAL_STATUS = cp_cal_status;
SELECT *
FROM IGS_GE_S_LOG_ENTRY
WHERE s_log_type = cp_s_log_type
AND creation_dt = cp_creation_dt
AND key = cp_key
AND text = cp_text;
SELECT ROWID
FROM IGS_GE_S_LOG_ENTRY
WHERE s_log_type = gv_log_type
AND creation_dt = gv_log_creation_dt
AND key = gv_log_key
AND text = v_other_detail;
IGS_CA_DA_INST_OFST_PKG.INSERT_ROW(
X_ROWID => X_ROWID,
X_DT_ALIAS => v_ins_dt_alias,
X_dai_sequence_number => v_ins_dai_sequence_number,
X_CAL_TYPE => v_ins_cal_type,
X_ci_sequence_number => v_ins_ci_sequence_number,
X_offset_dt_alias => v_ins_offset_dt_alias,
X_offset_dai_sequence_number => v_ins_offset_dai_seq_num,
X_offset_cal_type => v_ins_offset_cal_type,
X_offset_ci_sequence_number => v_ins_offset_ci_seq_num,
X_day_offset => p_day_offset,
X_week_offset => p_week_offset,
X_month_offset => p_month_offset,
X_year_offset => p_year_offset,
X_ofst_override => p_ofst_override,
X_MODE => 'R');
IGS_GE_S_LOG_ENTRY_PKG.DELETE_ROW(v_IGS_GE_S_LOG_ENTRY_CUR.ROWID);
IGS_GE_S_LOG_ENTRY_PKG.DELETE_ROW(v_IGS_GE_S_LOG_ENTRY_CUR.ROWID);
SELECT IGS_CA_DA_INST_SEQ_NUM_S.nextval
FROM DUAL;
SELECT *
FROM IGS_CA_DA_INST
WHERE CAL_TYPE = cp_cal_type AND
ci_sequence_number = cp_ci_sequence_number;
SELECT *
FROM IGS_CA_DA_INST_OFST
WHERE DT_ALIAS = cp_dt_alias AND
dai_sequence_number = cp_dai_sequence_number AND
CAL_TYPE = cp_cal_type AND
ci_sequence_number = cp_ci_sequence_number;
SELECT *
FROM IGS_CA_DA_INST_OFST
WHERE offset_dt_alias = cp_dt_alias AND
offset_dai_sequence_number = cp_dai_seq_num AND
offset_cal_type = cp_cal_type AND
offset_ci_sequence_number = cp_ci_seq;
SELECT *
FROM IGS_CA_DA_INST_PAIR
WHERE DT_ALIAS = cp_dt_alias AND
dai_sequence_number = cp_dai_sequence_number AND
CAL_TYPE = cp_cal_type AND
ci_sequence_number = cp_ci_sequence_number;
SELECT *
FROM IGS_CA_DA_INST_PAIR
WHERE related_dt_alias = cp_dt_alias AND
related_dai_sequence_number = cp_dai_seq_num AND
related_cal_type = cp_cal_type AND
related_ci_sequence_number = cp_ci_seq;
IGS_CA_DA_INST_PKG.INSERT_ROW(
X_ROWId => X_ROWID,
X_DT_ALIAS => v_new_dai_dt_alias,
X_sequence_number => v_new_sequence_number,
X_CAL_TYPE => p_rollover_cal_type,
X_ci_sequence_number => p_rollover_ci_sequence_number,
X_absolute_val => v_new_absolute_val,
X_MODE => 'R');
SELECT cir.sup_cal_type,
cir.sup_ci_sequence_number,
cir.load_research_percentage
FROM IGS_CA_INST_REL cir
WHERE cir.sub_cal_type = p_cal_type AND
cir.sub_ci_sequence_number = p_sequence_number;
SELECT ci.start_dt,
ci.end_dt,
ci.CAL_TYPE
FROM IGS_CA_INST ci
WHERE ci.CAL_TYPE = cp_cal_type AND
ci.sequence_number = cp_sequence_number;
SELECT ci.CAL_TYPE,
ci.sequence_number,
cs.s_cal_status
FROM IGS_CA_INST ci,
IGS_CA_STAT cs
WHERE ci.CAL_TYPE = cp_cal_type AND
ci.start_dt = cp_start_dt AND
ci.end_dt = cp_end_dt AND
ci.CAL_STATUS = cs.CAL_STATUS;
SELECT 'x'
FROM IGS_CA_INST_REL cir
WHERE cir.sub_cal_type = cp_sub_cal_type AND
cir.sub_ci_sequence_number = cp_sub_ci_sequence_number AND
cir.sup_cal_type = cp_sup_cal_type AND
cir.sup_ci_sequence_number = cp_sup_ci_sequence_number;
SELECT SLE.rowid
FROM IGS_GE_S_LOG_ENTRY sle
WHERE sle.s_log_type = cp_s_log_type AND
sle.creation_dt = cp_creation_dt AND
sle.key = cp_key AND
sle.text = cp_text
FOR UPDATE OF
sle.s_log_type,
sle.creation_dt,
sle.sequence_number,
sle.key,
sle.message_name,
sle.text NOWAIT;
IGS_CA_INST_REL_PKG.INSERT_ROW(
X_ROWID => X_ROWID,
X_sub_cal_type => p_sub_cal_type,
X_sub_ci_sequence_number => p_sub_ci_sequence_number,
X_sup_cal_type => v_derived_cal_instance_rec.CAL_TYPE,
X_sup_ci_sequence_number => v_derived_cal_instance_rec.sequence_number,
X_load_research_percentage => v_cal_inst_rltsp_sup_rec.load_research_percentage,
X_MODE => 'R');
IGS_GE_S_LOG_ENTRY_PKG.DELETE_ROW(p_rowid);
IGS_CA_INST_REL_PKG.INSERT_ROW(
X_ROWID => X_ROWID,
X_sub_cal_type => p_sub_cal_type,
X_sub_ci_sequence_number => p_sub_ci_sequence_number,
X_sup_cal_type => p_sup_cal_type,
X_sup_ci_sequence_number => p_sup_ci_sequence_number,
X_load_research_percentage => v_sup_load_res_percentage,
X_MODE => 'R');
IGS_GE_S_LOG_ENTRY_PKG.DELETE_ROW(v_c_dummy_rec.ROWID);
SELECT *
FROM IGS_CA_INST
WHERE CAL_TYPE = p_cal_type AND
sequence_number = p_sequence_number;
SELECT *
FROM IGS_CA_STAT
WHERE CAL_STATUS = cp_cal_status;
SELECT *
FROM IGS_CA_INST
WHERE CAL_TYPE = p_cal_type AND
sequence_number = p_sequence_number;
SELECT *
FROM IGS_CA_INST
WHERE CAL_TYPE = cp_cal_type AND
start_dt = cp_start_dt AND
end_dt = cp_end_dt;
SELECT *
FROM IGS_CA_STAT
WHERE s_cal_status = cp_cal_status;
SELECT *
FROM IGS_CA_TYPE
WHERE CAL_TYPE = cp_cal_type;
SELECT IGS_CA_INST_SEQ_NUM_S.nextval
FROM DUAL;
SELECT igs_ca_inst_seq_num_s1.nextval from DUAL;
SELECT count(*)
FROM IGS_CA_INST CI , IGS_CA_TYPE CAT
WHERE CAT.CAL_TYPE = CI.CAL_TYPE
AND CAT.S_CAL_CAT IN ('ACADEMIC' ,'LOAD','TEACHING')
AND CI.ALTERNATE_CODE = p_alternate_code;
IGS_CA_INST_PKG.INSERT_ROW(
X_ROWID => X_ROWID,
X_CAL_TYPE => v_new_cal_type,
X_sequence_number => v_new_sequence_number,
X_start_dt => v_new_start_dt,
X_end_dt => v_new_end_dt,
X_CAL_STATUS => v_new_cal_status,
X_SUP_CAL_STATUS_DIFFER_IND => NULL,
X_alternate_code => v_new_alternate_code,
X_prior_ci_sequence_number => p_sequence_number,
X_MODE => 'R',
X_SS_DISPLAYED => v_cal_instance_rec.ss_displayed,
X_ORG_ID => l_n_org_id,
X_DESCRIPTION => NULL,
X_TERM_INSTRUCTION_TIME => v_new_term_instruction_time,
X_PLANNING_FLAG => v_cal_instance_rec.planning_flag,
X_SCHEDULE_FLAG => v_cal_instance_rec.schedule_flag,
X_ADMIN_FLAG => v_cal_instance_rec.admin_flag
);
SELECT IGS_CA_INST_REL_SUP_CI_SNO_S1.NEXTVAL
FROM dual;
SELECT *
FROM IGS_CA_INST_REL
WHERE sup_cal_type = p_cal_type AND
sup_ci_sequence_number = p_sequence_number;
SELECT *
FROM IGS_CA_INST_REL cir1
WHERE (cir1.sup_cal_type,cir1.sup_ci_sequence_number) IN
(SELECT DISTINCT cir2.sub_cal_type,cir2.sub_ci_sequence_number
FROM IGS_CA_INST_REL cir2
WHERE cir2.sup_cal_type = p_cal_type AND
cir2.sup_ci_sequence_number = p_sequence_number);
SELECT *
FROM IGS_CA_INST_REL cir1
WHERE (cir1.sup_cal_type,cir1.sup_ci_sequence_number) IN
(SELECT DISTINCT cir2.sub_cal_type,cir2.sub_ci_sequence_number
FROM IGS_CA_INST_REL cir2
WHERE (cir2.sup_cal_type,cir2.sup_ci_sequence_number) IN
(SELECT DISTINCT cir3.sub_cal_type,cir3.sub_ci_sequence_number
FROM IGS_CA_INST_REL cir3
WHERE cir3.sup_cal_type = p_cal_type AND
cir3.sup_ci_sequence_number = p_sequence_number));
SELECT *
FROM IGS_CA_INST_REL cir1
WHERE (cir1.sup_cal_type,cir1.sup_ci_sequence_number) IN
(SELECT DISTINCT cir2.sub_cal_type,cir2.sub_ci_sequence_number
FROM IGS_CA_INST_REL cir2
WHERE (cir2.sup_cal_type,cir2.sup_ci_sequence_number) IN
(SELECT DISTINCT cir3.sub_cal_type,cir3.sub_ci_sequence_number
FROM IGS_CA_INST_REL cir3
WHERE (cir3.sup_cal_type,cir3.sup_ci_sequence_number) IN
(SELECT DISTINCT cir4.sub_cal_type,cir4.sub_ci_sequence_number
FROM IGS_CA_INST_REL cir4
WHERE cir4.sup_cal_type = p_cal_type AND
cir4.sup_ci_sequence_number = p_sequence_number)));
SELECT *
FROM IGS_CA_INST_REL cir1
WHERE (cir1.sup_cal_type,cir1.sup_ci_sequence_number) IN
(SELECT DISTINCT cir2.sub_cal_type,cir2.sub_ci_sequence_number
FROM IGS_CA_INST_REL cir2
WHERE (cir2.sup_cal_type,cir2.sup_ci_sequence_number) IN
(SELECT DISTINCT cir3.sub_cal_type,cir3.sub_ci_sequence_number
FROM IGS_CA_INST_REL cir3
WHERE (cir3.sup_cal_type,cir3.sup_ci_sequence_number) IN
(SELECT DISTINCT cir4.sub_cal_type,cir4.sub_ci_sequence_number
FROM IGS_CA_INST_REL cir4
WHERE (cir4.sup_cal_type,cir4.sup_ci_sequence_number) IN
(SELECT DISTINCT cir5.sub_cal_type,cir5.sub_ci_sequence_number
FROM IGS_CA_INST_REL cir5
WHERE cir5.sup_cal_type = p_cal_type AND
cir5.sup_ci_sequence_number = p_sequence_number))));
t1_rollover_ci.DELETE;
t2_rollover_ci.DELETE;
t1_rollover_ci.DELETE;
t2_rollover_ci.DELETE;