[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_last_update_date IN DATE ,
x_last_updated_by IN NUMBER ,
x_last_update_login IN NUMBER ,
x_org_id IN NUMBER ,
x_ss_enrol_ind IN VARCHAR2 ,
x_ivr_enrol_ind IN VARCHAR2 ,
x_claimable_hours IN NUMBER ,
x_auditable_ind IN VARCHAR2 ,
x_audit_permission_ind IN VARCHAR2 ,
x_max_auditors_allowed IN NUMBER ,
x_billing_credit_points IN NUMBER ,
x_ovrd_wkld_val_flag IN VARCHAR2 ,
x_workload_val_code IN VARCHAR2 ,
x_billing_hrs IN NUMBER
) AS
CURSOR cur_old_ref_values IS
SELECT *
FROM IGS_PS_UNIT_VER_HIST_ALL
WHERE rowid = x_rowid;
IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
Close cur_old_ref_values;
Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
IF (p_action = 'UPDATE') THEN
new_references.creation_date := old_references.creation_date;
new_references.last_update_date := x_last_update_date;
new_references.last_updated_by := x_last_updated_by;
new_references.last_update_login := x_last_update_login;
SELECT rowid
FROM IGS_PS_UNIT_VER_HIST_ALL
WHERE unit_cd = x_unit_cd
AND version_number = x_version_number
AND hist_start_dt = x_hist_start_dt
FOR UPDATE NOWAIT;
x_last_update_date IN DATE ,
x_last_updated_by IN NUMBER ,
x_last_update_login IN NUMBER ,
x_org_id IN NUMBER ,
x_ss_enrol_ind IN VARCHAR2 ,
x_ivr_enrol_ind IN VARCHAR2 ,
x_claimable_hours IN NUMBER ,
x_auditable_ind IN VARCHAR2 ,
x_audit_permission_ind IN VARCHAR2 ,
x_max_auditors_allowed IN NUMBER ,
x_billing_credit_points IN NUMBER ,
x_ovrd_wkld_val_flag IN VARCHAR2 ,
x_workload_val_code IN VARCHAR2 ,
x_billing_hrs IN NUMBER
) AS
l_count NUMBER;
SELECT 'Y'
FROM IGS_PS_UNIT_VER_HIST
WHERE unit_cd = cp_unit_cd
AND version_number = cp_version_number
AND hist_start_dt = cp_hist_start_dt;
x_last_update_date,
x_last_updated_by,
x_last_update_login,
x_org_id ,
x_ss_enrol_ind,
x_ivr_enrol_ind,
x_claimable_hours,
x_auditable_ind,
x_audit_permission_ind,
x_max_auditors_allowed,
x_billing_credit_points,
x_ovrd_wkld_val_flag,
x_workload_val_code,
x_billing_hrs
);
IF (p_action = 'INSERT') THEN
-- Call all the procedures related to Before Insert.
IF Get_PK_For_Validation (New_References.unit_cd,
New_References.version_number,
New_References.hist_start_dt) THEN
-- Bug 5455027, When updating the Unit Record repetedly the Unique Key fails due to
-- hist_start_date becomes same as last update date of the Unit Record
-- Here increment the histroy start date by 1 second till unique combination is found.
l_count := 1;
ELSIF (p_action = 'UPDATE') THEN
-- Call all the procedures related to Before Update.
Check_Constraints;
ELSIF (p_action = 'VALIDATE_INSERT') THEN
IF Get_PK_For_Validation (New_References.unit_cd,
New_References.version_number,
New_References.hist_start_dt) THEN
Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
ELSIF (p_action = 'VALIDATE_UPDATE') THEN
Check_Constraints;
PROCEDURE INSERT_ROW (
X_ROWID in out NOCOPY VARCHAR2,
X_UNIT_CD in VARCHAR2,
X_VERSION_NUMBER in NUMBER,
X_HIST_START_DT in DATE,
X_HIST_END_DT in DATE,
X_HIST_WHO in NUMBER,
X_START_DT in DATE,
X_REVIEW_DT in DATE,
X_EXPIRY_DT in DATE,
X_END_DT in DATE,
X_UNIT_STATUS in VARCHAR2,
X_TITLE in VARCHAR2,
X_SHORT_TITLE in VARCHAR2,
X_TITLE_OVERRIDE_IND in VARCHAR2,
X_ABBREVIATION in VARCHAR2,
X_UNIT_LEVEL in VARCHAR2,
X_UL_DESCRIPTION in VARCHAR2,
X_CREDIT_POINT_DESCRIPTOR in VARCHAR2,
X_ENROLLED_CREDIT_POINTS in NUMBER,
X_POINTS_OVERRIDE_IND in VARCHAR2,
X_SUPP_EXAM_PERMITTED_IND in VARCHAR2,
X_COORD_PERSON_ID in NUMBER,
X_OWNER_ORG_UNIT_CD in VARCHAR2,
X_OWNER_OU_START_DT in DATE,
X_OU_DESCRIPTION in VARCHAR2,
X_AWARD_COURSE_ONLY_IND in VARCHAR2,
X_RESEARCH_UNIT_IND in VARCHAR2,
X_INDUSTRIAL_IND in VARCHAR2,
X_PRACTICAL_IND in VARCHAR2,
X_REPEATABLE_IND in VARCHAR2,
X_ASSESSABLE_IND in VARCHAR2,
X_ACHIEVABLE_CREDIT_POINTS in NUMBER,
X_POINTS_INCREMENT in NUMBER,
X_POINTS_MIN in NUMBER,
X_POINTS_MAX in NUMBER,
X_UNIT_INT_COURSE_LEVEL_CD in VARCHAR2,
X_UICL_DESCRIPTION in VARCHAR2,
x_subtitle_id IN NUMBER ,
x_work_load_other IN NUMBER ,
x_contact_hrs_lecture IN NUMBER ,
x_contact_hrs_lab IN NUMBER ,
x_contact_hrs_other IN NUMBER ,
x_non_schd_required_hrs IN NUMBER ,
x_exclude_from_max_cp_limit IN VARCHAR2 ,
x_record_exclusion_flag IN VARCHAR2 ,
x_ss_display_ind IN VARCHAR2 ,
x_cal_type_enrol_load_cal IN VARCHAR2 ,
x_sequence_num_enrol_load_cal IN NUMBER ,
x_cal_type_offer_load_cal IN VARCHAR2 ,
x_sequence_num_offer_load_cal IN NUMBER ,
x_curriculum_id IN VARCHAR2 ,
x_override_enrollment_max IN NUMBER ,
x_rpt_fmly_id IN NUMBER ,
x_repeat_code IN VARCHAR2 ,
x_unit_type_id IN NUMBER ,
x_level_code IN VARCHAR2 ,
x_advance_maximum IN NUMBER ,
x_approval_date IN DATE ,
x_continuing_education_units IN NUMBER ,
x_enrollment_expected IN NUMBER ,
x_enrollment_maximum IN NUMBER ,
x_enrollment_minimum IN NUMBER ,
x_federal_financial_aid IN VARCHAR2 ,
x_institutional_financial_aid IN VARCHAR2 ,
x_lab_credit_points IN NUMBER ,
x_lecture_credit_points IN NUMBER ,
x_max_repeats_for_credit IN NUMBER ,
x_max_repeats_for_funding IN NUMBER ,
x_max_repeat_credit_points IN NUMBER ,
x_clock_hours IN NUMBER ,
x_other_credit_points IN NUMBER ,
x_same_teaching_period IN VARCHAR2 ,
x_same_teach_period_repeats IN NUMBER ,
x_same_teach_period_repeats_cp IN NUMBER ,
x_state_financial_aid IN VARCHAR2 ,
x_work_load_cp_lab IN NUMBER ,
x_work_load_cp_lecture IN NUMBER ,
x_subtitle_modifiable_flag IN VARCHAR2 ,
x_subtitle IN VARCHAR2 ,
x_special_permission_ind IN VARCHAR2 ,
X_MODE IN VARCHAR2 ,
X_ORG_ID IN NUMBER,
X_SS_ENROL_IND IN VARCHAR2 ,
X_IVR_ENROL_IND IN VARCHAR2 ,
x_claimable_hours IN NUMBER ,
x_auditable_ind IN VARCHAR2 ,
x_audit_permission_ind IN VARCHAR2 ,
x_max_auditors_allowed IN NUMBER ,
x_billing_credit_points IN NUMBER ,
x_ovrd_wkld_val_flag IN VARCHAR2 ,
x_workload_val_code IN VARCHAR2 ,
x_billing_hrs IN NUMBER
) AS
cursor C is select ROWID from IGS_PS_UNIT_VER_HIST_ALL
where UNIT_CD = X_UNIT_CD
and VERSION_NUMBER = X_VERSION_NUMBER
and HIST_START_DT = X_HIST_START_DT;
X_LAST_UPDATE_DATE DATE;
X_LAST_UPDATED_BY NUMBER;
X_LAST_UPDATE_LOGIN NUMBER;
X_LAST_UPDATE_DATE := SYSDATE;
X_LAST_UPDATED_BY := 1;
X_LAST_UPDATE_LOGIN := 0;
X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
if X_LAST_UPDATED_BY is NULL then
X_LAST_UPDATED_BY := -1;
X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
if X_LAST_UPDATE_LOGIN is NULL then
X_LAST_UPDATE_LOGIN := -1;
p_action => 'INSERT',
x_rowid => X_ROWID,
x_unit_cd => X_UNIT_CD,
x_version_number => X_VERSION_NUMBER,
x_hist_start_dt => X_HIST_START_DT,
x_hist_end_dt => X_HIST_END_DT,
x_hist_who => X_HIST_WHO,
x_start_dt => X_START_DT,
x_review_dt => X_REVIEW_DT,
x_expiry_dt => X_EXPIRY_DT,
x_end_dt => X_END_DT,
x_unit_status => X_UNIT_STATUS,
x_title => X_TITLE,
x_short_title => X_SHORT_TITLE,
x_title_override_ind => X_TITLE_OVERRIDE_IND,
x_abbreviation => X_ABBREVIATION,
x_unit_level => X_UNIT_LEVEL,
x_ul_description => X_UL_DESCRIPTION,
x_credit_point_descriptor => X_CREDIT_POINT_DESCRIPTOR,
x_enrolled_credit_points => X_ENROLLED_CREDIT_POINTS,
x_points_override_ind => X_POINTS_OVERRIDE_IND,
x_supp_exam_permitted_ind => NVL(X_SUPP_EXAM_PERMITTED_IND,'Y'),
x_coord_person_id => X_COORD_PERSON_ID,
x_owner_org_unit_cd => X_OWNER_ORG_UNIT_CD,
x_owner_ou_start_dt => X_OWNER_OU_START_DT,
x_ou_description => X_OU_DESCRIPTION,
x_award_course_only_ind => X_AWARD_COURSE_ONLY_IND,
x_research_unit_ind => X_RESEARCH_UNIT_IND,
x_industrial_ind => X_INDUSTRIAL_IND,
x_practical_ind => X_PRACTICAL_IND,
x_repeatable_ind => X_REPEATABLE_IND,
x_assessable_ind => X_ASSESSABLE_IND,
x_achievable_credit_points => X_ACHIEVABLE_CREDIT_POINTS,
x_points_increment => X_POINTS_INCREMENT,
x_points_min => X_POINTS_MIN,
x_points_max => X_POINTS_MAX,
x_unit_int_course_level_cd => X_UNIT_INT_COURSE_LEVEL_CD,
x_uicl_description => X_UICL_DESCRIPTION,
x_subtitle_id => x_subtitle_id,
x_work_load_other => x_work_load_other,
x_contact_hrs_lecture => x_contact_hrs_lecture,
x_contact_hrs_lab => x_contact_hrs_lab,
x_contact_hrs_other => x_contact_hrs_other,
x_non_schd_required_hrs => x_non_schd_required_hrs,
x_exclude_from_max_cp_limit => x_exclude_from_max_cp_limit,
x_record_exclusion_flag => x_record_exclusion_flag,
x_ss_display_ind => x_ss_display_ind,
x_cal_type_enrol_load_cal => x_cal_type_enrol_load_cal,
x_sequence_num_enrol_load_cal => x_sequence_num_enrol_load_cal,
x_cal_type_offer_load_cal => x_cal_type_offer_load_cal,
x_sequence_num_offer_load_cal => x_sequence_num_offer_load_cal,
x_curriculum_id => x_curriculum_id,
x_override_enrollment_max => x_override_enrollment_max,
x_rpt_fmly_id => x_rpt_fmly_id,
x_repeat_code => x_repeat_code,
x_unit_type_id => x_unit_type_id,
x_level_code => x_level_code,
x_advance_maximum => x_advance_maximum,
x_approval_date => x_approval_date,
x_continuing_education_units => x_continuing_education_units,
x_enrollment_expected => x_enrollment_expected,
x_enrollment_maximum => x_enrollment_maximum,
x_enrollment_minimum => x_enrollment_minimum,
x_federal_financial_aid => x_federal_financial_aid,
x_institutional_financial_aid => x_institutional_financial_aid,
x_lab_credit_points => x_lab_credit_points,
x_lecture_credit_points => x_lecture_credit_points,
x_max_repeats_for_credit => x_max_repeats_for_credit,
x_max_repeats_for_funding => x_max_repeats_for_funding,
x_max_repeat_credit_points => x_max_repeat_credit_points,
x_clock_hours => x_clock_hours,
x_other_credit_points => x_other_credit_points,
x_same_teaching_period => x_same_teaching_period,
x_same_teach_period_repeats => x_same_teach_period_repeats,
x_same_teach_period_repeats_cp => x_same_teach_period_repeats_cp,
x_state_financial_aid => x_state_financial_aid,
x_work_load_cp_lab => x_work_load_cp_lab,
x_work_load_cp_lecture => x_work_load_cp_lecture,
x_subtitle_modifiable_flag => x_subtitle_modifiable_flag,
x_subtitle => x_subtitle,
x_special_permission_ind => x_special_permission_ind,
x_creation_date => X_LAST_UPDATE_DATE,
x_created_by => X_LAST_UPDATED_BY,
x_last_update_date => X_LAST_UPDATE_DATE,
x_last_updated_by => X_LAST_UPDATED_BY,
x_last_update_login => X_LAST_UPDATE_LOGIN,
x_org_id => igs_ge_gen_003.get_org_id,
x_ss_enrol_ind => X_SS_ENROL_IND,
x_ivr_enrol_ind => X_IVR_ENROL_IND,
x_claimable_hours => x_claimable_hours,
x_auditable_ind => x_auditable_ind,
x_audit_permission_ind => x_audit_permission_ind,
x_max_auditors_allowed => x_max_auditors_allowed,
x_billing_credit_points => x_billing_credit_points,
x_ovrd_wkld_val_flag => x_ovrd_wkld_val_flag,
x_workload_val_code => x_workload_val_code,
x_billing_hrs => x_billing_hrs
);
INSERT INTO IGS_PS_UNIT_VER_HIST_ALL (
UNIT_CD,
VERSION_NUMBER,
HIST_START_DT,
HIST_END_DT,
HIST_WHO,
START_DT,
REVIEW_DT,
EXPIRY_DT,
END_DT,
UNIT_STATUS,
TITLE,
SHORT_TITLE,
TITLE_OVERRIDE_IND,
ABBREVIATION,
UNIT_LEVEL,
UL_DESCRIPTION,
CREDIT_POINT_DESCRIPTOR,
ENROLLED_CREDIT_POINTS,
POINTS_OVERRIDE_IND,
SUPP_EXAM_PERMITTED_IND,
COORD_PERSON_ID,
OWNER_ORG_UNIT_CD,
OWNER_OU_START_DT,
OU_DESCRIPTION,
AWARD_COURSE_ONLY_IND,
RESEARCH_UNIT_IND,
INDUSTRIAL_IND,
PRACTICAL_IND,
REPEATABLE_IND,
ASSESSABLE_IND,
ACHIEVABLE_CREDIT_POINTS,
POINTS_INCREMENT,
POINTS_MIN,
POINTS_MAX,
UNIT_INT_COURSE_LEVEL_CD,
UICL_DESCRIPTION,
subtitle_id,
work_load_other,
contact_hrs_lecture,
contact_hrs_lab,
contact_hrs_other,
non_schd_required_hrs,
exclude_from_max_cp_limit,
record_exclusion_flag,
ss_display_ind,
cal_type_enrol_load_cal,
sequence_num_enrol_load_cal,
cal_type_offer_load_cal,
sequence_num_offer_load_cal,
curriculum_id,
override_enrollment_max,
rpt_fmly_id,
repeat_code,
unit_type_id,
level_code,
advance_maximum,
approval_date,
continuing_education_units,
enrollment_expected,
enrollment_maximum,
enrollment_minimum,
federal_financial_aid,
institutional_financial_aid,
lab_credit_points,
lecture_credit_points,
max_repeats_for_credit,
max_repeats_for_funding,
max_repeat_credit_points,
clock_hours,
other_credit_points,
same_teaching_period,
same_teach_period_repeats,
same_teach_period_repeats_cp,
state_financial_aid,
work_load_cp_lab,
work_load_cp_lecture,
subtitle_modifiable_flag,
subtitle,
special_permission_ind,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
ORG_ID,
SS_ENROL_IND,
IVR_ENROL_IND,
claimable_hours,
auditable_ind,
audit_permission_ind,
max_auditors_allowed,
billing_credit_points,
ovrd_wkld_val_flag,
workload_val_code,
billing_hrs
) values (
NEW_REFERENCES.UNIT_CD,
NEW_REFERENCES.VERSION_NUMBER,
NEW_REFERENCES.HIST_START_DT,
NEW_REFERENCES.HIST_END_DT,
NEW_REFERENCES.HIST_WHO,
NEW_REFERENCES.START_DT,
NEW_REFERENCES.REVIEW_DT,
NEW_REFERENCES.EXPIRY_DT,
NEW_REFERENCES.END_DT,
NEW_REFERENCES.UNIT_STATUS,
NEW_REFERENCES.TITLE,
NEW_REFERENCES.SHORT_TITLE,
NEW_REFERENCES.TITLE_OVERRIDE_IND,
NEW_REFERENCES.ABBREVIATION,
NEW_REFERENCES.UNIT_LEVEL,
NEW_REFERENCES.UL_DESCRIPTION,
NEW_REFERENCES.CREDIT_POINT_DESCRIPTOR,
NEW_REFERENCES.ENROLLED_CREDIT_POINTS,
NEW_REFERENCES.POINTS_OVERRIDE_IND,
NEW_REFERENCES.SUPP_EXAM_PERMITTED_IND,
NEW_REFERENCES.COORD_PERSON_ID,
NEW_REFERENCES.OWNER_ORG_UNIT_CD,
NEW_REFERENCES.OWNER_OU_START_DT,
NEW_REFERENCES.OU_DESCRIPTION,
NEW_REFERENCES.AWARD_COURSE_ONLY_IND,
NEW_REFERENCES.RESEARCH_UNIT_IND,
NEW_REFERENCES.INDUSTRIAL_IND,
NEW_REFERENCES.PRACTICAL_IND,
NEW_REFERENCES.REPEATABLE_IND,
NEW_REFERENCES.ASSESSABLE_IND,
NEW_REFERENCES.ACHIEVABLE_CREDIT_POINTS,
NEW_REFERENCES.POINTS_INCREMENT,
NEW_REFERENCES.POINTS_MIN,
NEW_REFERENCES.POINTS_MAX,
NEW_REFERENCES.UNIT_INT_COURSE_LEVEL_CD,
NEW_REFERENCES.UICL_DESCRIPTION,
new_references.subtitle_id,
new_references.work_load_other,
new_references.contact_hrs_lecture,
new_references.contact_hrs_lab,
new_references.contact_hrs_other,
new_references.non_schd_required_hrs,
new_references.exclude_from_max_cp_limit,
new_references.record_exclusion_flag,
new_references.ss_display_ind,
new_references.cal_type_enrol_load_cal,
new_references.sequence_num_enrol_load_cal,
new_references.cal_type_offer_load_cal,
new_references.sequence_num_offer_load_cal,
new_references.curriculum_id,
new_references.override_enrollment_max,
new_references.rpt_fmly_id,
new_references.repeat_code,
new_references.unit_type_id,
new_references.level_code,
new_references.advance_maximum,
new_references.approval_date,
new_references.continuing_education_units,
new_references.enrollment_expected,
new_references.enrollment_maximum,
new_references.enrollment_minimum,
new_references.federal_financial_aid,
new_references.institutional_financial_aid,
new_references.lab_credit_points,
new_references.lecture_credit_points,
new_references.max_repeats_for_credit,
new_references.max_repeats_for_funding,
new_references.max_repeat_credit_points,
new_references.clock_hours,
new_references.other_credit_points,
new_references.same_teaching_period,
new_references.same_teach_period_repeats,
new_references.same_teach_period_repeats_cp,
new_references.state_financial_aid,
new_references.work_load_cp_lab,
new_references.work_load_cp_lecture,
new_references.subtitle_modifiable_flag,
new_references.subtitle,
new_references.special_permission_ind,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN,
NEW_REFERENCES.ORG_ID,
NEW_REFERENCES.SS_ENROL_IND,
NEW_REFERENCES.IVR_ENROL_IND,
new_references.claimable_hours,
new_references.auditable_ind,
new_references.audit_permission_ind,
new_references.max_auditors_allowed,
new_references.billing_credit_points,
new_references.ovrd_wkld_val_flag,
new_references.workload_val_code,
new_references.billing_hrs
);
p_action => 'INSERT',
x_rowid => X_ROWID
);
END INSERT_ROW;
cursor c1 is select
HIST_END_DT,
HIST_WHO,
START_DT,
REVIEW_DT,
EXPIRY_DT,
END_DT,
UNIT_STATUS,
TITLE,
SHORT_TITLE,
TITLE_OVERRIDE_IND,
ABBREVIATION,
UNIT_LEVEL,
UL_DESCRIPTION,
CREDIT_POINT_DESCRIPTOR,
ENROLLED_CREDIT_POINTS,
POINTS_OVERRIDE_IND,
SUPP_EXAM_PERMITTED_IND,
COORD_PERSON_ID,
OWNER_ORG_UNIT_CD,
OWNER_OU_START_DT,
OU_DESCRIPTION,
AWARD_COURSE_ONLY_IND,
RESEARCH_UNIT_IND,
INDUSTRIAL_IND,
PRACTICAL_IND,
REPEATABLE_IND,
ASSESSABLE_IND,
ACHIEVABLE_CREDIT_POINTS,
POINTS_INCREMENT,
POINTS_MIN,
POINTS_MAX,
UNIT_INT_COURSE_LEVEL_CD,
UICL_DESCRIPTION,
subtitle_id,
work_load_other,
contact_hrs_lecture,
contact_hrs_lab,
contact_hrs_other,
non_schd_required_hrs,
exclude_from_max_cp_limit,
record_exclusion_flag,
ss_display_ind,
cal_type_enrol_load_cal,
sequence_num_enrol_load_cal,
cal_type_offer_load_cal,
sequence_num_offer_load_cal,
curriculum_id,
override_enrollment_max,
rpt_fmly_id,
repeat_code,
unit_type_id,
level_code,
advance_maximum,
approval_date,
continuing_education_units,
enrollment_expected,
enrollment_maximum,
enrollment_minimum,
federal_financial_aid,
institutional_financial_aid,
lab_credit_points,
lecture_credit_points,
max_repeats_for_credit,
max_repeats_for_funding,
max_repeat_credit_points,
clock_hours,
other_credit_points,
same_teaching_period,
same_teach_period_repeats,
same_teach_period_repeats_cp,
state_financial_aid,
work_load_cp_lab,
work_load_cp_lecture,
subtitle_modifiable_flag,
subtitle,
special_permission_ind,
SS_ENROL_IND,
IVR_ENROL_IND,
claimable_hours,
auditable_ind,
audit_permission_ind,
max_auditors_allowed,
billing_credit_points,
ovrd_wkld_val_flag,
workload_val_code,
billing_hrs
from IGS_PS_UNIT_VER_HIST_ALL
where ROWID = X_ROWID for update nowait;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
PROCEDURE UPDATE_ROW (
X_ROWID in VARCHAR2,
X_UNIT_CD in VARCHAR2,
X_VERSION_NUMBER in NUMBER,
X_HIST_START_DT in DATE,
X_HIST_END_DT in DATE,
X_HIST_WHO in NUMBER,
X_START_DT in DATE,
X_REVIEW_DT in DATE,
X_EXPIRY_DT in DATE,
X_END_DT in DATE,
X_UNIT_STATUS in VARCHAR2,
X_TITLE in VARCHAR2,
X_SHORT_TITLE in VARCHAR2,
X_TITLE_OVERRIDE_IND in VARCHAR2,
X_ABBREVIATION in VARCHAR2,
X_UNIT_LEVEL in VARCHAR2,
X_UL_DESCRIPTION in VARCHAR2,
X_CREDIT_POINT_DESCRIPTOR in VARCHAR2,
X_ENROLLED_CREDIT_POINTS in NUMBER,
X_POINTS_OVERRIDE_IND in VARCHAR2,
X_SUPP_EXAM_PERMITTED_IND in VARCHAR2,
X_COORD_PERSON_ID in NUMBER,
X_OWNER_ORG_UNIT_CD in VARCHAR2,
X_OWNER_OU_START_DT in DATE,
X_OU_DESCRIPTION in VARCHAR2,
X_AWARD_COURSE_ONLY_IND in VARCHAR2,
X_RESEARCH_UNIT_IND in VARCHAR2,
X_INDUSTRIAL_IND in VARCHAR2,
X_PRACTICAL_IND in VARCHAR2,
X_REPEATABLE_IND in VARCHAR2,
X_ASSESSABLE_IND in VARCHAR2,
X_ACHIEVABLE_CREDIT_POINTS in NUMBER,
X_POINTS_INCREMENT in NUMBER,
X_POINTS_MIN in NUMBER,
X_POINTS_MAX in NUMBER,
X_UNIT_INT_COURSE_LEVEL_CD in VARCHAR2,
X_UICL_DESCRIPTION in VARCHAR2,
x_subtitle_id IN NUMBER ,
x_work_load_other IN NUMBER ,
x_contact_hrs_lecture IN NUMBER ,
x_contact_hrs_lab IN NUMBER ,
x_contact_hrs_other IN NUMBER ,
x_non_schd_required_hrs IN NUMBER ,
x_exclude_from_max_cp_limit IN VARCHAR2 ,
x_record_exclusion_flag IN VARCHAR2 ,
x_ss_display_ind IN VARCHAR2 ,
x_cal_type_enrol_load_cal IN VARCHAR2 ,
x_sequence_num_enrol_load_cal IN NUMBER ,
x_cal_type_offer_load_cal IN VARCHAR2 ,
x_sequence_num_offer_load_cal IN NUMBER ,
x_curriculum_id IN VARCHAR2 ,
x_override_enrollment_max IN NUMBER ,
x_rpt_fmly_id IN NUMBER ,
x_repeat_code IN VARCHAR2 ,
x_unit_type_id IN NUMBER ,
x_level_code IN VARCHAR2 ,
x_advance_maximum IN NUMBER ,
x_approval_date IN DATE ,
x_continuing_education_units IN NUMBER ,
x_enrollment_expected IN NUMBER ,
x_enrollment_maximum IN NUMBER ,
x_enrollment_minimum IN NUMBER ,
x_federal_financial_aid IN VARCHAR2 ,
x_institutional_financial_aid IN VARCHAR2 ,
x_lab_credit_points IN NUMBER ,
x_lecture_credit_points IN NUMBER ,
x_max_repeats_for_credit IN NUMBER ,
x_max_repeats_for_funding IN NUMBER ,
x_max_repeat_credit_points IN NUMBER ,
x_clock_hours IN NUMBER ,
x_other_credit_points IN NUMBER ,
x_same_teaching_period IN VARCHAR2 ,
x_same_teach_period_repeats IN NUMBER ,
x_same_teach_period_repeats_cp IN NUMBER ,
x_state_financial_aid IN VARCHAR2 ,
x_work_load_cp_lab IN NUMBER ,
x_work_load_cp_lecture IN NUMBER ,
x_subtitle_modifiable_flag IN VARCHAR2 ,
x_subtitle IN VARCHAR2 ,
x_special_permission_ind IN VARCHAR2 ,
X_MODE IN VARCHAR2 ,
X_SS_ENROL_IND IN VARCHAR2 ,
X_IVR_ENROL_IND IN VARCHAR2 ,
x_claimable_hours IN NUMBER ,
x_auditable_ind IN VARCHAR2 ,
x_audit_permission_ind IN VARCHAR2 ,
x_max_auditors_allowed IN NUMBER,
x_billing_credit_points IN NUMBER ,
x_ovrd_wkld_val_flag IN VARCHAR2 ,
x_workload_val_code IN VARCHAR2 ,
x_billing_hrs IN NUMBER
) AS
X_LAST_UPDATE_DATE DATE;
X_LAST_UPDATED_BY NUMBER;
X_LAST_UPDATE_LOGIN NUMBER;
X_LAST_UPDATE_DATE := SYSDATE;
X_LAST_UPDATED_BY := 1;
X_LAST_UPDATE_LOGIN := 0;
X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
if X_LAST_UPDATED_BY is NULL then
X_LAST_UPDATED_BY := -1;
X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
if X_LAST_UPDATE_LOGIN is NULL then
X_LAST_UPDATE_LOGIN := -1;
p_action => 'UPDATE',
x_rowid => X_ROWID,
x_unit_cd => X_UNIT_CD,
x_version_number => X_VERSION_NUMBER,
x_hist_start_dt => X_HIST_START_DT,
x_hist_end_dt => X_HIST_END_DT,
x_hist_who => X_HIST_WHO,
x_start_dt => X_START_DT,
x_review_dt => X_REVIEW_DT,
x_expiry_dt => X_EXPIRY_DT,
x_end_dt => X_END_DT,
x_unit_status => X_UNIT_STATUS,
x_title => X_TITLE,
x_short_title => X_SHORT_TITLE,
x_title_override_ind => X_TITLE_OVERRIDE_IND,
x_abbreviation => X_ABBREVIATION,
x_unit_level => X_UNIT_LEVEL,
x_ul_description => X_UL_DESCRIPTION,
x_credit_point_descriptor => X_CREDIT_POINT_DESCRIPTOR,
x_enrolled_credit_points => X_ENROLLED_CREDIT_POINTS,
x_points_override_ind => X_POINTS_OVERRIDE_IND,
x_supp_exam_permitted_ind => X_SUPP_EXAM_PERMITTED_IND,
x_coord_person_id => X_COORD_PERSON_ID,
x_owner_org_unit_cd => X_OWNER_ORG_UNIT_CD,
x_owner_ou_start_dt => X_OWNER_OU_START_DT,
x_ou_description => X_OU_DESCRIPTION,
x_award_course_only_ind => X_AWARD_COURSE_ONLY_IND,
x_research_unit_ind => X_RESEARCH_UNIT_IND,
x_industrial_ind => X_INDUSTRIAL_IND,
x_practical_ind => X_PRACTICAL_IND,
x_repeatable_ind => X_REPEATABLE_IND,
x_assessable_ind => X_ASSESSABLE_IND,
x_achievable_credit_points => X_ACHIEVABLE_CREDIT_POINTS,
x_points_increment => X_POINTS_INCREMENT,
x_points_min => X_POINTS_MIN,
x_points_max => X_POINTS_MAX,
x_unit_int_course_level_cd => X_UNIT_INT_COURSE_LEVEL_CD,
x_uicl_description => X_UICL_DESCRIPTION,
x_subtitle_id => x_subtitle_id,
x_work_load_other => x_work_load_other,
x_contact_hrs_lecture => x_contact_hrs_lecture,
x_contact_hrs_lab => x_contact_hrs_lab,
x_contact_hrs_other => x_contact_hrs_other,
x_non_schd_required_hrs => x_non_schd_required_hrs,
x_exclude_from_max_cp_limit => x_exclude_from_max_cp_limit,
x_record_exclusion_flag => x_record_exclusion_flag,
x_ss_display_ind => x_ss_display_ind,
x_cal_type_enrol_load_cal => x_cal_type_enrol_load_cal,
x_sequence_num_enrol_load_cal => x_sequence_num_enrol_load_cal,
x_cal_type_offer_load_cal => x_cal_type_offer_load_cal,
x_sequence_num_offer_load_cal => x_sequence_num_offer_load_cal,
x_curriculum_id => x_curriculum_id,
x_override_enrollment_max => x_override_enrollment_max,
x_rpt_fmly_id => x_rpt_fmly_id,
x_repeat_code => x_repeat_code,
x_unit_type_id => x_unit_type_id,
x_level_code => x_level_code,
x_advance_maximum => x_advance_maximum,
x_approval_date => x_approval_date,
x_continuing_education_units => x_continuing_education_units,
x_enrollment_expected => x_enrollment_expected,
x_enrollment_maximum => x_enrollment_maximum,
x_enrollment_minimum => x_enrollment_minimum,
x_federal_financial_aid => x_federal_financial_aid,
x_institutional_financial_aid => x_institutional_financial_aid,
x_lab_credit_points => x_lab_credit_points,
x_lecture_credit_points => x_lecture_credit_points,
x_max_repeats_for_credit => x_max_repeats_for_credit,
x_max_repeats_for_funding => x_max_repeats_for_funding,
x_max_repeat_credit_points => x_max_repeat_credit_points,
x_clock_hours => x_clock_hours,
x_other_credit_points => x_other_credit_points,
x_same_teaching_period => x_same_teaching_period,
x_same_teach_period_repeats => x_same_teach_period_repeats,
x_same_teach_period_repeats_cp => x_same_teach_period_repeats_cp,
x_state_financial_aid => x_state_financial_aid,
x_work_load_cp_lab => x_work_load_cp_lab,
x_work_load_cp_lecture => x_work_load_cp_lecture,
x_subtitle_modifiable_flag => x_subtitle_modifiable_flag,
x_subtitle => x_subtitle,
x_special_permission_ind => x_special_permission_ind,
x_creation_date => X_LAST_UPDATE_DATE,
x_created_by => X_LAST_UPDATED_BY,
x_last_update_date => X_LAST_UPDATE_DATE,
x_last_updated_by => X_LAST_UPDATED_BY,
x_last_update_login => X_LAST_UPDATE_LOGIN,
x_ss_enrol_ind => X_SS_ENROL_IND,
x_ivr_enrol_ind => X_IVR_ENROL_IND,
x_claimable_hours => x_claimable_hours,
x_auditable_ind => x_auditable_ind,
x_audit_permission_ind => x_audit_permission_ind,
x_max_auditors_allowed => x_max_auditors_allowed,
x_billing_credit_points => x_billing_credit_points,
x_ovrd_wkld_val_flag => x_ovrd_wkld_val_flag ,
x_workload_val_code => x_workload_val_code,
x_billing_hrs => x_billing_hrs
);
UPDATE IGS_PS_UNIT_VER_HIST_ALL SET
HIST_END_DT = NEW_REFERENCES.HIST_END_DT,
HIST_WHO = NEW_REFERENCES.HIST_WHO,
START_DT = NEW_REFERENCES.START_DT,
REVIEW_DT = NEW_REFERENCES.REVIEW_DT,
EXPIRY_DT = NEW_REFERENCES.EXPIRY_DT,
END_DT = NEW_REFERENCES.END_DT,
UNIT_STATUS = NEW_REFERENCES.UNIT_STATUS,
TITLE = NEW_REFERENCES.TITLE,
SHORT_TITLE = NEW_REFERENCES.SHORT_TITLE,
TITLE_OVERRIDE_IND = NEW_REFERENCES.TITLE_OVERRIDE_IND,
ABBREVIATION = NEW_REFERENCES.ABBREVIATION,
UNIT_LEVEL = NEW_REFERENCES.UNIT_LEVEL,
UL_DESCRIPTION = NEW_REFERENCES.UL_DESCRIPTION,
CREDIT_POINT_DESCRIPTOR = NEW_REFERENCES.CREDIT_POINT_DESCRIPTOR,
ENROLLED_CREDIT_POINTS = NEW_REFERENCES.ENROLLED_CREDIT_POINTS,
POINTS_OVERRIDE_IND = NEW_REFERENCES.POINTS_OVERRIDE_IND,
SUPP_EXAM_PERMITTED_IND = NEW_REFERENCES.SUPP_EXAM_PERMITTED_IND,
COORD_PERSON_ID = NEW_REFERENCES.COORD_PERSON_ID,
OWNER_ORG_UNIT_CD = NEW_REFERENCES.OWNER_ORG_UNIT_CD,
OWNER_OU_START_DT = NEW_REFERENCES.OWNER_OU_START_DT,
OU_DESCRIPTION = NEW_REFERENCES.OU_DESCRIPTION,
AWARD_COURSE_ONLY_IND = NEW_REFERENCES.AWARD_COURSE_ONLY_IND,
RESEARCH_UNIT_IND = NEW_REFERENCES.RESEARCH_UNIT_IND,
INDUSTRIAL_IND = NEW_REFERENCES.INDUSTRIAL_IND,
PRACTICAL_IND = NEW_REFERENCES.PRACTICAL_IND,
REPEATABLE_IND = NEW_REFERENCES.REPEATABLE_IND,
ASSESSABLE_IND = NEW_REFERENCES.ASSESSABLE_IND,
ACHIEVABLE_CREDIT_POINTS = NEW_REFERENCES.ACHIEVABLE_CREDIT_POINTS,
POINTS_INCREMENT = NEW_REFERENCES.POINTS_INCREMENT,
POINTS_MIN = NEW_REFERENCES.POINTS_MIN,
POINTS_MAX = NEW_REFERENCES.POINTS_MAX,
UNIT_INT_COURSE_LEVEL_CD = NEW_REFERENCES.UNIT_INT_COURSE_LEVEL_CD,
UICL_DESCRIPTION = NEW_REFERENCES.UICL_DESCRIPTION,
subtitle_id = new_references.subtitle_id,
work_load_other = new_references.work_load_other,
contact_hrs_lecture = new_references.contact_hrs_lecture,
contact_hrs_lab = new_references.contact_hrs_lab,
contact_hrs_other = new_references.contact_hrs_other,
non_schd_required_hrs = new_references.non_schd_required_hrs,
exclude_from_max_cp_limit = new_references.exclude_from_max_cp_limit,
record_exclusion_flag = new_references.record_exclusion_flag,
ss_display_ind = new_references.ss_display_ind,
cal_type_enrol_load_cal = new_references.cal_type_enrol_load_cal,
sequence_num_enrol_load_cal = new_references.sequence_num_enrol_load_cal,
cal_type_offer_load_cal = new_references.cal_type_offer_load_cal,
sequence_num_offer_load_cal = new_references.sequence_num_offer_load_cal,
curriculum_id = new_references.curriculum_id,
override_enrollment_max = new_references.override_enrollment_max,
rpt_fmly_id = new_references.rpt_fmly_id,
repeat_code = new_references.repeat_code,
unit_type_id = new_references.unit_type_id,
level_code = new_references.level_code,
advance_maximum = new_references.advance_maximum,
approval_date = new_references.approval_date,
continuing_education_units = new_references.continuing_education_units,
enrollment_expected = new_references.enrollment_expected,
enrollment_maximum = new_references.enrollment_maximum,
enrollment_minimum = new_references.enrollment_minimum,
federal_financial_aid = new_references.federal_financial_aid,
institutional_financial_aid = new_references.institutional_financial_aid,
lab_credit_points = new_references.lab_credit_points,
lecture_credit_points = new_references.lecture_credit_points,
max_repeats_for_credit = new_references.max_repeats_for_credit,
max_repeats_for_funding = new_references.max_repeats_for_funding,
max_repeat_credit_points = new_references.max_repeat_credit_points,
clock_hours = new_references.clock_hours,
other_credit_points = new_references.other_credit_points,
same_teaching_period = new_references.same_teaching_period,
same_teach_period_repeats = new_references.same_teach_period_repeats,
same_teach_period_repeats_cp = new_references.same_teach_period_repeats_cp,
state_financial_aid = new_references.state_financial_aid,
work_load_cp_lab = new_references.work_load_cp_lab,
work_load_cp_lecture = new_references.work_load_cp_lecture,
subtitle_modifiable_flag = new_references.subtitle_modifiable_flag,
subtitle = new_references.subtitle,
special_permission_ind = new_references.special_permission_ind,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
SS_ENROL_IND = X_SS_ENROL_IND,
IVR_ENROL_IND = IVR_ENROL_IND,
claimable_hours = new_references.claimable_hours,
auditable_ind = new_references.auditable_ind,
audit_permission_ind = new_references.audit_permission_ind,
max_auditors_allowed = new_references.max_auditors_allowed,
billing_credit_points = new_references.billing_credit_points,
ovrd_wkld_val_flag = new_references.ovrd_wkld_val_flag,
workload_val_code = new_references.workload_val_code ,
billing_hrs = new_references.billing_hrs
where ROWID = X_ROWID
;
p_action => 'UPDATE',
x_rowid => X_ROWID
);
END UPDATE_ROW;
cursor c1 is select rowid from IGS_PS_UNIT_VER_HIST_ALL
where UNIT_CD = X_UNIT_CD
and VERSION_NUMBER = X_VERSION_NUMBER
and HIST_START_DT = X_HIST_START_DT
;
INSERT_ROW (
X_ROWID,
X_UNIT_CD,
X_VERSION_NUMBER,
X_HIST_START_DT,
X_HIST_END_DT,
X_HIST_WHO,
X_START_DT,
X_REVIEW_DT,
X_EXPIRY_DT,
X_END_DT,
X_UNIT_STATUS,
X_TITLE,
X_SHORT_TITLE,
X_TITLE_OVERRIDE_IND,
X_ABBREVIATION,
X_UNIT_LEVEL,
X_UL_DESCRIPTION,
X_CREDIT_POINT_DESCRIPTOR,
X_ENROLLED_CREDIT_POINTS,
X_POINTS_OVERRIDE_IND,
X_SUPP_EXAM_PERMITTED_IND,
X_COORD_PERSON_ID,
X_OWNER_ORG_UNIT_CD,
X_OWNER_OU_START_DT,
X_OU_DESCRIPTION,
X_AWARD_COURSE_ONLY_IND,
X_RESEARCH_UNIT_IND,
X_INDUSTRIAL_IND,
X_PRACTICAL_IND,
X_REPEATABLE_IND,
X_ASSESSABLE_IND,
X_ACHIEVABLE_CREDIT_POINTS,
X_POINTS_INCREMENT,
X_POINTS_MIN,
X_POINTS_MAX,
X_UNIT_INT_COURSE_LEVEL_CD,
X_UICL_DESCRIPTION,
x_subtitle_id,
x_work_load_other,
x_contact_hrs_lecture,
x_contact_hrs_lab,
x_contact_hrs_other,
x_non_schd_required_hrs,
x_exclude_from_max_cp_limit,
x_record_exclusion_flag,
x_ss_display_ind,
x_cal_type_enrol_load_cal,
x_sequence_num_enrol_load_cal,
x_cal_type_offer_load_cal,
x_sequence_num_offer_load_cal,
x_curriculum_id,
x_override_enrollment_max,
x_rpt_fmly_id,
x_repeat_code,
x_unit_type_id,
x_level_code,
x_advance_maximum,
x_approval_date,
x_continuing_education_units,
x_enrollment_expected,
x_enrollment_maximum,
x_enrollment_minimum,
x_federal_financial_aid,
x_institutional_financial_aid,
x_lab_credit_points,
x_lecture_credit_points,
x_max_repeats_for_credit,
x_max_repeats_for_funding,
x_max_repeat_credit_points,
x_clock_hours,
x_other_credit_points,
x_same_teaching_period,
x_same_teach_period_repeats,
x_same_teach_period_repeats_cp,
x_state_financial_aid,
x_work_load_cp_lab,
x_work_load_cp_lecture,
x_subtitle_modifiable_flag,
x_subtitle,
x_special_permission_ind,
X_MODE,
X_ORG_ID,
X_SS_ENROL_IND,
X_IVR_ENROL_IND,
x_claimable_hours,
x_auditable_ind,
x_audit_permission_ind,
x_max_auditors_allowed,
x_billing_credit_points,
x_ovrd_wkld_val_flag,
x_workload_val_code,
x_billing_hrs
);
UPDATE_ROW (
X_ROWID,
X_UNIT_CD,
X_VERSION_NUMBER,
X_HIST_START_DT,
X_HIST_END_DT,
X_HIST_WHO,
X_START_DT,
X_REVIEW_DT,
X_EXPIRY_DT,
X_END_DT,
X_UNIT_STATUS,
X_TITLE,
X_SHORT_TITLE,
X_TITLE_OVERRIDE_IND,
X_ABBREVIATION,
X_UNIT_LEVEL,
X_UL_DESCRIPTION,
X_CREDIT_POINT_DESCRIPTOR,
X_ENROLLED_CREDIT_POINTS,
X_POINTS_OVERRIDE_IND,
X_SUPP_EXAM_PERMITTED_IND,
X_COORD_PERSON_ID,
X_OWNER_ORG_UNIT_CD,
X_OWNER_OU_START_DT,
X_OU_DESCRIPTION,
X_AWARD_COURSE_ONLY_IND,
X_RESEARCH_UNIT_IND,
X_INDUSTRIAL_IND,
X_PRACTICAL_IND,
X_REPEATABLE_IND,
X_ASSESSABLE_IND,
X_ACHIEVABLE_CREDIT_POINTS,
X_POINTS_INCREMENT,
X_POINTS_MIN,
X_POINTS_MAX,
X_UNIT_INT_COURSE_LEVEL_CD,
X_UICL_DESCRIPTION,
x_subtitle_id,
x_work_load_other,
x_contact_hrs_lecture,
x_contact_hrs_lab,
x_contact_hrs_other,
x_non_schd_required_hrs,
x_exclude_from_max_cp_limit,
x_record_exclusion_flag,
x_ss_display_ind,
x_cal_type_enrol_load_cal,
x_sequence_num_enrol_load_cal,
x_cal_type_offer_load_cal,
x_sequence_num_offer_load_cal,
x_curriculum_id,
x_override_enrollment_max,
x_rpt_fmly_id,
x_repeat_code,
x_unit_type_id,
x_level_code,
x_advance_maximum,
x_approval_date,
x_continuing_education_units,
x_enrollment_expected,
x_enrollment_maximum,
x_enrollment_minimum,
x_federal_financial_aid,
x_institutional_financial_aid,
x_lab_credit_points,
x_lecture_credit_points,
x_max_repeats_for_credit,
x_max_repeats_for_funding,
x_max_repeat_credit_points,
x_clock_hours,
x_other_credit_points,
x_same_teaching_period,
x_same_teach_period_repeats,
x_same_teach_period_repeats_cp,
x_state_financial_aid,
x_work_load_cp_lab,
x_work_load_cp_lecture,
x_subtitle_modifiable_flag,
x_subtitle,
x_special_permission_ind,
X_MODE,
X_SS_ENROL_IND,
X_IVR_ENROL_IND,
x_claimable_hours,
x_auditable_ind,
x_audit_permission_ind,
x_max_auditors_allowed,
x_billing_credit_points,
x_ovrd_wkld_val_flag,
x_workload_val_code,
x_billing_hrs
);
PROCEDURE DELETE_ROW (
X_ROWID in VARCHAR2
) AS
BEGIN
Before_DML (
p_action => 'DELETE',
x_rowid => X_ROWID
);
delete from IGS_PS_UNIT_VER_HIST_ALL
where ROWID = X_ROWID;
p_action => 'DELETE',
x_rowid => X_ROWID
);
END DELETE_ROW;