[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_selection_dt IN DATE ,
x_student_confirmed_ind IN VARCHAR2 ,
x_end_dt IN DATE ,
x_parent_unit_set_cd IN VARCHAR2 ,
x_parent_sequence_number IN NUMBER ,
x_primary_set_ind IN VARCHAR2 ,
x_voluntary_end_ind IN VARCHAR2 ,
x_authorised_person_id IN NUMBER ,
x_authorised_on IN DATE ,
x_override_title IN VARCHAR2 ,
x_rqrmnts_complete_ind IN VARCHAR2 ,
x_rqrmnts_complete_dt IN DATE ,
x_s_completed_source_type IN VARCHAR2 ,
x_catalog_cal_type IN VARCHAR2 ,
x_catalog_seq_num IN NUMBER ,
x_attribute_category IN VARCHAR2 ,
x_attribute1 IN VARCHAR2 ,
x_attribute2 IN VARCHAR2 ,
x_attribute3 IN VARCHAR2 ,
x_attribute4 IN VARCHAR2 ,
x_attribute5 IN VARCHAR2 ,
x_attribute6 IN VARCHAR2 ,
x_attribute7 IN VARCHAR2 ,
x_attribute8 IN VARCHAR2 ,
x_attribute9 IN VARCHAR2 ,
x_attribute10 IN VARCHAR2 ,
x_attribute11 IN VARCHAR2 ,
x_attribute12 IN VARCHAR2 ,
x_attribute13 IN VARCHAR2 ,
x_attribute14 IN VARCHAR2 ,
x_attribute15 IN VARCHAR2 ,
x_attribute16 IN VARCHAR2 ,
x_attribute17 IN VARCHAR2 ,
x_attribute18 IN VARCHAR2 ,
x_attribute19 IN VARCHAR2 ,
x_attribute20 IN VARCHAR2 ,
x_creation_date IN DATE ,
x_created_by IN NUMBER ,
x_last_update_date IN DATE ,
x_last_updated_by IN NUMBER ,
x_last_update_login IN NUMBER
) AS
CURSOR cur_old_ref_values IS
SELECT *
FROM IGS_AS_SU_SETATMPT
WHERE ROWID = x_rowid;
IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
new_references.selection_dt := x_selection_dt;
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;
PROCEDURE BeforeRowInsertUpdateDelete1(
p_inserting IN BOOLEAN ,
p_updating IN BOOLEAN ,
p_deleting IN BOOLEAN
) AS
-- check whether the unit set is a valid one for the prorgam offering option
CURSOR c_us_valid IS
SELECT 'x'
FROM IGS_PS_OFR_OPT_UNIT_SET_V usoo,
IGS_EN_STDNT_PS_ATT spa
WHERE spa.person_id = new_references.person_id
AND spa.course_cd = new_references.course_cd
AND usoo.unit_set_cd = new_references.unit_set_cd
AND usoo.us_version_number = new_references.us_version_number
AND usoo.coo_id = spa.coo_id;
IF p_inserting THEN
-- Validate the the IGS_PS_UNIT set is able to be created.
-- against the student IGS_PS_COURSE attempt.
IF IGS_EN_VAL_SUSA.enrp_val_susa_sca(
new_references.person_id,
new_references.course_cd,
v_message_name ) = FALSE THEN
Fnd_Message.Set_Name('IGS', v_message_name);
IF p_inserting OR
(p_updating AND
((NVL(new_references.authorised_person_id, 0) <>
NVL(old_references.authorised_person_id, 0)) OR
(NVL(new_references.authorised_on,IGS_GE_DATE.IGSDATE('1900/01/01'))
<> NVL(old_references.authorised_on, IGS_GE_DATE.IGSDATE('1900/01/01'))))) THEN
-- p_legacy value passed as 'N' as function is called in non-legacy mode
IF IGS_EN_VAL_SUSA.enrp_val_susa_auth(
new_references.unit_set_cd,
new_references.us_version_number,
new_references.end_dt,
new_references.authorised_person_id,
new_references.authorised_on,
v_message_name,
'N') = FALSE THEN
Fnd_Message.Set_Name('IGS', v_message_name);
IF p_inserting OR
(p_updating AND
((NVL(new_references.authorised_person_id, 0) <>
NVL(old_references.authorised_person_id, 0)) OR
(new_references.student_confirmed_ind <> old_references.student_confirmed_ind) OR
(NVL(new_references.authorised_on,IGS_GE_DATE.IGSDATE('1900/01/01'))
<> NVL(old_references.authorised_on, IGS_GE_DATE.IGSDATE('1900/01/01'))))) THEN
-- Validate that the authorisation fields must be set when
-- the IGS_PS_UNIT set cd requires authorisation (IGS_EN_UNIT_SET.authorisation_ind = 'Y')
-- Check required only when the IGS_PS_UNIT set is confirmed.
IF (new_references.student_confirmed_ind = 'Y') THEN
IF IGS_EN_VAL_SUSA.enrp_val_susa_us_ath(
new_references.unit_set_cd,
new_references.us_version_number,
new_references.authorised_person_id,
new_references.authorised_on,
v_message_name ) = FALSE THEN
Fnd_Message.Set_Name('IGS', v_message_name);
IF p_inserting OR
(p_updating AND
((NVL(new_references.rqrmnts_complete_ind, 'x')
<> NVL(old_references.rqrmnts_complete_ind, 'x')) OR
(NVL(new_references.rqrmnts_complete_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
<> NVL(old_references.rqrmnts_complete_dt,
IGS_GE_DATE.IGSDATE('1900/01/01')))))THEN
-- p_legacy value passed as 'N' as function is called in non-legacy mode
IF IGS_EN_VAL_SUSA.enrp_val_susa_cmplt(
new_references.rqrmnts_complete_dt,
new_references.rqrmnts_complete_ind,
new_references.student_confirmed_ind,
v_message_name,
'N') = FALSE THEN
Fnd_Message.Set_Name('IGS', v_message_name);
IF p_inserting OR
(p_updating AND
((NVL(new_references.rqrmnts_complete_ind, 'x')
<> NVL(old_references.rqrmnts_complete_ind, 'x')) OR
(NVL(new_references.s_completed_source_type, 'x')
<> NVL(old_references.s_completed_source_type, 'x')) OR
(NVL(new_references.rqrmnts_complete_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
<> NVL(old_references.rqrmnts_complete_dt,
IGS_GE_DATE.IGSDATE('1900/01/01')))))THEN
IF IGS_EN_VAL_SUSA.enrp_val_susa_scst(
new_references.rqrmnts_complete_dt,
new_references.rqrmnts_complete_ind,
new_references.s_completed_source_type,
v_message_name ) = FALSE THEN
Fnd_Message.Set_Name('IGS', v_message_name);
IF p_inserting OR
(p_updating AND
((new_references.student_confirmed_ind <> old_references.student_confirmed_ind) OR
(NVL(new_references.selection_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
<> NVL(old_references.selection_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))))) THEN
-- p_legacy value passed as 'N' as function is called in non-legacy mode
IF IGS_EN_VAL_SUSA.enrp_val_susa_sci_sd(
new_references.student_confirmed_ind,
new_references.selection_dt,
v_message_name,
'N') = FALSE THEN
Fnd_Message.Set_Name('IGS', v_message_name);
IF p_inserting OR
(p_updating AND
((new_references.voluntary_end_ind <> old_references.voluntary_end_ind) OR
(NVL(new_references.end_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
<> NVL(old_references.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))))) THEN
IF IGS_EN_VAL_SUSA.enrp_val_susa_end_vi(
new_references.voluntary_end_ind,
new_references.end_dt,
v_message_name ) = FALSE THEN
Fnd_Message.Set_Name('IGS', v_message_name);
END BeforeRowInsertUpdateDelete1;
p_inserting IN BOOLEAN ,
p_updating IN BOOLEAN ,
p_deleting IN BOOLEAN ,
p_parent_unit_set_cd IN VARCHAR2 ,
p_end_dt IN DATE ,
p_student_confirmed_ind IN VARCHAR2 ,
p_primary_set_ind IN VARCHAR2
) AS
v_insert BOOLEAN;
v_update BOOLEAN;
v_delete BOOLEAN;
IF p_inserting THEN
-- Validate the the unit set is able to be created
-- with the unit set status being valid and the
-- expiry date not set. If set then person must have
-- previously selected it.
IF IGS_EN_VAL_SUSA.enrp_val_susa_us_act(
new_references.person_id,
new_references.course_cd,
new_references.unit_set_cd,
new_references.sequence_number,
new_references.us_version_number,
v_message_name) = FALSE THEN
Fnd_Message.Set_Name('IGS', v_message_name);
IF (p_inserting OR p_updating) AND
p_parent_unit_set_cd IS NOT NULL THEN
-- Validate if the unit set is to be defined as a subordinate or if
-- relationship specified, that it is valid within the course offering.
-- p_legacy value passed as 'N' as function is called in non-legacy mode
IF IGS_EN_VAL_SUSA.enrp_val_susa_cousr(
new_references.person_id,
new_references.course_cd,
new_references.unit_set_cd,
new_references.us_version_number,
new_references.parent_unit_set_cd,
new_references.parent_sequence_number,
cst_error,
v_message_name,
'N') = FALSE THEN
Fnd_Message.Set_Name('IGS', v_message_name);
IF (p_inserting OR p_updating) AND
p_end_dt IS NOT NULL THEN
-- Validate the end date, check if the authorisation details
-- need to be set or if more than one open end dated instance
-- of the unit set exists. Also cannot be cleared if parent ended.
-- If part of the admissions offer, authorisation required to end
-- the unit set.
-- p_legacy value passed as 'N' as function is called in non-legacy mode
IF IGS_EN_VAL_SUSA.enrp_val_susa_end_dt(
new_references.person_id,
new_references.course_cd,
new_references.unit_set_cd,
new_references.sequence_number,
new_references.us_version_number,
new_references.end_dt,
new_references.authorised_person_id,
new_references.authorised_on,
new_references.parent_unit_set_cd,
new_references.parent_sequence_number,
cst_error,
v_message_name,
'N') = FALSE THEN
-- Check if warning message returned.
IF v_message_name <> 'IGS_EN_UNITSET_REQ_AUTHORISAT' THEN
Fnd_Message.Set_Name('IGS', v_message_name);
IF (p_inserting OR p_updating) AND
p_student_confirmed_ind IS NOT NULL THEN
-- Validate that the unit set is not confirmed when the student course
-- attempt is unconfirmed.
-- Also check that not unset one end date or complete date set. Cannot be
-- confirmed and linked to a parent that is unconfirmed. Cannot be
-- confirmed if encumbrances exist.
-- p_legacy value passed as 'N' as function is called in non-legacy mode
IF IGS_EN_VAL_SUSA.enrp_val_susa_sci(
new_references.person_id,
new_references.course_cd,
new_references.unit_set_cd,
new_references.sequence_number,
new_references.us_version_number,
new_references.parent_unit_set_cd,
new_references.parent_sequence_number,
new_references.student_confirmed_ind,
new_references.selection_dt,
new_references.end_dt,
new_references.rqrmnts_complete_ind,
v_message_name,
'N') = FALSE THEN
Fnd_Message.Set_Name('IGS', v_message_name);
IF (p_inserting OR p_updating) AND
p_primary_set_ind IS NOT NULL THEN
-- Validate the primary set indicator is only set for
-- non-administrative sets and that there does not already
-- exist a unit set that has a higher rank.
-- p_legacy value passed as 'N' as function is called in non-legacy mode
IF IGS_EN_VAL_SUSA.enrp_val_susa_prmry(
new_references.person_id,
new_references.course_cd,
new_references.unit_set_cd,
new_references.us_version_number,
new_references.primary_set_ind,
v_message_name,
'N') = FALSE THEN
Fnd_Message.Set_Name('IGS', v_message_name);
PROCEDURE AfterRowInsertUpdate2(
p_inserting IN BOOLEAN,
p_updating IN BOOLEAN,
p_deleting IN BOOLEAN
) AS
------------------------------------------------------------------------------
-- Change History:
-- Who When What
-- SVANUKUR 27-NOV-2003 added logic to create a TODO record of the TODO type FEE_RECALC
-- If a record is being inserted/updated/deleted
------------------------------------------------------------------------------
v_tmp_end_dt DATE;
SELECT 'X'
FROM IGS_EN_UNIT_SET us,
IGS_EN_UNIT_SET_CAT usc
WHERE us.unit_set_cd = cp_unit_set_cd
AND us.unit_set_cat = usc.unit_set_cat
AND usc.unit_set_cat = 'PRE-ENROLL';
IF p_inserting THEN
-- Validate the the IGS_PS_UNIT set is able to be created
-- with the IGS_PS_UNIT set status being valid and the
-- expiry date not set. If set then IGS_PE_PERSON must have
-- previously selected it. (IGS_EN_VAL_SUSA.enrp_val_susa_us_act)
-- Cannot call modules because trigger will be mutating.
-- Save the rowid of the current row.
RowValMutation(
p_inserting ,
p_updating ,
p_deleting ,
NULL ,
NULL ,
'N',
'N'
);
IF p_inserting OR
(p_updating AND
((NVL(new_references.parent_unit_set_cd, 'NULL')
<> NVL(old_references.parent_unit_set_cd, 'NULL')) OR
(NVL(new_references.parent_sequence_number, 0)
<> NVL(old_references.parent_sequence_number, 0)))) THEN
-- Validate if the IGS_PS_UNIT set is to be defined as a subordinate or if
-- relationship specified, that it is valid within the IGS_PS_COURSE offering
-- (IGS_EN_VAL_SUSA.enrp_val_susa_cousr).
-- Validate if the parent IGS_PS_UNIT set has a null end date, IGS_PS_UNIT set is
-- not being linked to itself (directly or indirectly). Cannot be
-- confirmed if parent is unconfirmed (IGS_EN_VAL_SUSA.enrp_val_susa_parent).
-- Cannot call modules because trigger will be mutating.
-- Save the rowid of the current row setting the parent IGS_PS_UNIT set field to
-- indicate to perform parent IGS_PS_UNIT set code validation
RowValMutation(
p_inserting ,
p_updating ,
p_deleting ,
new_references.parent_unit_set_cd ,
NULL ,
'N',
'N'
);
IF p_inserting OR
(NVL(new_references.end_dt,IGS_GE_DATE.IGSDATE('1900/01/01'))
<> NVL(old_references.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))) THEN
-- Store away the rowid as the validation will cause a mutating trigger.
-- Set the end date field to indicate validation required for end date.
-- If p_inserting, validation is still to occur. If end_is null then set it
-- such that validation will happen in the after statement trigger.
IF p_inserting AND
(NVL(new_references.end_dt, IGS_GE_DATE.IGSDATE('1900/01/01'))
= IGS_GE_DATE.IGSDATE('1900/01/01')) THEN
v_tmp_end_dt := IGS_GE_DATE.IGSDATE('1900/01/01');
p_inserting ,
p_updating ,
p_deleting ,
NULL ,
v_tmp_end_dt,
'N',
'N'
);
IF (p_inserting AND new_references.student_confirmed_ind = 'Y') OR
(p_updating AND
(new_references.student_confirmed_ind <> old_references.student_confirmed_ind)) THEN
-- Validate that the IGS_PS_UNIT set is not confirmed when the student IGS_PS_COURSE
-- attempt is unconfirmed.
-- Also check that not unset one end date or complete date set. Cannot be
-- confirmed and linked to a parent that is unconfirmed. Cannot be
-- confirmed if encumbrances exist.
-- Cannot call modules because trigger will be mutating.
-- Save the rowid of the current row setting the student confirmed field to
-- indicate to perform student_confirmed_ind validation
RowValMutation(
p_inserting ,
p_updating ,
p_deleting ,
NULL ,
NULL,
new_references.student_confirmed_ind,
'N'
);
IF p_inserting OR
(p_updating AND
(new_references.primary_set_ind <> old_references.primary_set_ind)) THEN
-- Validate the primary set indicator is only set for
-- non-administrative sets and that there does not already
-- exist a IGS_PS_UNIT set that has a higher rank.
-- Cannot call modules because trigger will be mutating.
-- Save the rowid of the current row setting the primary_set field to
-- indicate to perform primary_set_ind validation
RowValMutation(
p_inserting ,
p_updating ,
p_deleting ,
NULL ,
NULL,
'N',
new_references.primary_set_ind
);
IF p_inserting OR p_updating THEN
v_unit_set := NULL;
END AfterRowInsertUpdate2;
PROCEDURE AfterRowUpdateDelete3(
p_inserting IN BOOLEAN,
p_updating IN BOOLEAN,
p_deleting IN BOOLEAN
) AS
v_message_name VARCHAR2(30);
new_references.selection_dt,
old_references.selection_dt,
new_references.student_confirmed_ind,
old_references.student_confirmed_ind,
new_references.end_dt,
old_references.end_dt,
new_references.parent_unit_set_cd,
old_references.parent_unit_set_cd,
new_references.parent_sequence_number,
old_references.parent_sequence_number,
new_references.primary_set_ind,
old_references.primary_set_ind,
new_references.voluntary_end_ind,
old_references.voluntary_end_ind,
new_references.authorised_person_id,
old_references.authorised_person_id,
new_references.authorised_on,
old_references.authorised_on,
new_references.override_title,
old_references.override_title,
new_references.rqrmnts_complete_ind,
old_references.rqrmnts_complete_ind,
new_references.rqrmnts_complete_dt,
old_references.rqrmnts_complete_dt,
new_references.s_completed_source_type,
old_references.s_completed_source_type,
new_references.catalog_cal_type ,
old_references.catalog_cal_type ,
new_references.catalog_seq_num ,
old_references.catalog_seq_num ,
new_references.last_updated_by,
old_references.last_updated_by,
new_references.last_update_date,
old_references.last_update_date);
END AfterRowUpdateDelete3;
SELECT 'x'
FROM IGS_PS_OF_UNT_SET_RL cousr,
IGS_AS_SU_SETATMPT susa,
IGS_EN_STDNT_PS_ATT spa
WHERE spa.person_id = susa.person_id AND
spa.course_cd = susa.course_cd AND
susa.person_id = new_references.person_id AND
susa.course_cd = new_references.course_cd AND
susa.unit_set_cd = new_references.parent_unit_set_cd AND
susa.sequence_number = new_references.parent_sequence_number AND
cousr.course_cd = spa.course_cd AND
cousr.crv_version_number = spa.version_number AND
cousr.cal_type = spa.cal_type AND
cousr.sub_unit_set_cd = new_references.unit_set_cd AND
cousr.sub_us_version_number = new_references.us_version_number AND
cousr.sup_unit_set_cd = susa.unit_set_cd AND
cousr.sup_us_version_number = susa.us_version_number;
Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
SELECT ROWID
FROM IGS_AS_SU_SETATMPT
WHERE person_id = x_person_id
AND course_cd = x_course_cd
AND unit_set_cd = x_unit_set_cd
AND sequence_number = x_sequence_number
FOR UPDATE NOWAIT;
SELECT ROWID
FROM IGS_AS_SU_SETATMPT
WHERE person_id = x_person_id
AND course_cd = x_course_cd ;
SELECT ROWID
FROM IGS_AS_SU_SETATMPT
WHERE person_id = x_person_id
AND course_cd = x_course_cd
AND parent_unit_set_cd = x_unit_set_cd
AND parent_sequence_number = x_sequence_number ;
SELECT ROWID
FROM IGS_AS_SU_SETATMPT
WHERE unit_set_cd = x_unit_set_cd
AND us_version_number = x_version_number ;
SELECT ROWID
FROM IGS_AS_SU_SETATMPT
WHERE CATALOG_CAL_TYPE = X_CATALOG_CAL_TYPE
AND CATALOG_SEQ_NUM = X_CATALOG_SEQ_NUM
;
SELECT a.rowid
FROM igs_as_su_setatmpt a, igs_en_stdnt_ps_att b
WHERE a.course_cd=b.course_cd
AND a.person_id=b.person_id
AND b.course_cd=x_course_cd
AND b.version_number=x_version_number
AND b.cal_type = x_cal_type
AND a.unit_set_cd = x_unit_set_cd
AND a.us_version_number = x_us_version_number ;
x_selection_dt IN DATE ,
x_student_confirmed_ind IN VARCHAR2 ,
x_end_dt IN DATE ,
x_parent_unit_set_cd IN VARCHAR2 ,
x_parent_sequence_number IN NUMBER ,
x_primary_set_ind IN VARCHAR2 ,
x_voluntary_end_ind IN VARCHAR2 ,
x_authorised_person_id IN NUMBER ,
x_authorised_on IN DATE ,
x_override_title IN VARCHAR2 ,
x_rqrmnts_complete_ind IN VARCHAR2 ,
x_rqrmnts_complete_dt IN DATE ,
x_s_completed_source_type IN VARCHAR2 ,
x_creation_date IN DATE ,
x_created_by IN NUMBER ,
x_last_update_date IN DATE ,
x_last_updated_by IN NUMBER ,
x_last_update_login IN NUMBER ,
x_catalog_cal_type IN VARCHAR2 ,
x_catalog_seq_num IN NUMBER ,
x_attribute_category IN VARCHAR2 ,
x_attribute1 IN VARCHAR2 ,
x_attribute2 IN VARCHAR2 ,
x_attribute3 IN VARCHAR2 ,
x_attribute4 IN VARCHAR2 ,
x_attribute5 IN VARCHAR2 ,
x_attribute6 IN VARCHAR2 ,
x_attribute7 IN VARCHAR2 ,
x_attribute8 IN VARCHAR2 ,
x_attribute9 IN VARCHAR2 ,
x_attribute10 IN VARCHAR2 ,
x_attribute11 IN VARCHAR2 ,
x_attribute12 IN VARCHAR2 ,
x_attribute13 IN VARCHAR2 ,
x_attribute14 IN VARCHAR2 ,
x_attribute15 IN VARCHAR2 ,
x_attribute16 IN VARCHAR2 ,
x_attribute17 IN VARCHAR2 ,
x_attribute18 IN VARCHAR2 ,
x_attribute19 IN VARCHAR2 ,
x_attribute20 IN VARCHAR2
) AS
BEGIN
Set_Column_Values (
p_action,
x_rowid,
x_person_id,
x_course_cd,
x_unit_set_cd,
x_us_version_number,
x_sequence_number,
x_selection_dt,
x_student_confirmed_ind,
x_end_dt,
x_parent_unit_set_cd,
x_parent_sequence_number,
x_primary_set_ind,
x_voluntary_end_ind,
x_authorised_person_id,
x_authorised_on,
x_override_title,
x_rqrmnts_complete_ind,
x_rqrmnts_complete_dt,
x_s_completed_source_type,
x_catalog_cal_type,
x_catalog_seq_num,
x_attribute_category,
x_attribute1,
x_attribute2,
x_attribute3,
x_attribute4,
x_attribute5,
x_attribute6,
x_attribute7,
x_attribute8,
x_attribute9,
x_attribute10,
x_attribute11,
x_attribute12,
x_attribute13,
x_attribute14,
x_attribute15,
x_attribute16,
x_attribute17,
x_attribute18,
x_attribute19,
x_attribute20,
x_creation_date,
x_created_by,
x_last_update_date,
x_last_updated_by,
x_last_update_login
);
IF (p_action = 'INSERT') THEN
-- Call all the procedures related to Before Insert.
BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE, p_updating=> FALSE, p_deleting=> FALSE );
ELSIF (p_action = 'UPDATE') THEN
-- Call all the procedures related to Before Update.
BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE, p_updating=> TRUE, p_deleting=> FALSE );
ELSIF (p_action = 'DELETE') THEN
-- Call all the procedures related to Before Delete.
BeforeRowInsertUpdateDelete1 ( p_inserting => FALSE, p_updating=> FALSE, p_deleting=> TRUE );
ELSIF (p_action = 'VALIDATE_INSERT') THEN
IF Get_PK_For_Validation (
new_references.person_id,
new_references.course_cd,
new_references.unit_set_cd,
new_references.sequence_number
) THEN
Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
ELSIF (p_action = 'VALIDATE_UPDATE') THEN
Check_Constraints;
ELSIF (p_action = 'VALIDATE_DELETE') THEN
Check_Child_Existance;
IF (p_action = 'INSERT') THEN
-- Call all the procedures related to After Insert.
AfterRowInsertUpdate2 ( p_inserting => TRUE, p_updating => FALSE, p_deleting => FALSE);
ELSIF (p_action = 'UPDATE') THEN
-- Call all the procedures related to After Update.
AfterRowInsertUpdate2 ( p_inserting => FALSE, p_updating => TRUE, p_deleting => FALSE);
AfterRowUpdateDelete3 ( p_inserting => FALSE, p_updating => TRUE, p_deleting => FALSE);
ELSIF (p_action = 'DELETE') THEN
-- Call all the procedures related to After Delete.
AfterRowUpdateDelete3 ( p_inserting => FALSE, p_updating => FALSE, p_deleting => TRUE);
AfterRowInsertUpdate2 ( p_inserting => FALSE, p_updating => FALSE, p_deleting => TRUE);
PROCEDURE INSERT_ROW (
X_ROWID IN OUT NOCOPY VARCHAR2,
X_PERSON_ID IN NUMBER,
X_COURSE_CD IN VARCHAR2,
X_UNIT_SET_CD IN VARCHAR2,
X_SEQUENCE_NUMBER IN NUMBER,
X_US_VERSION_NUMBER IN NUMBER,
X_SELECTION_DT IN DATE,
X_STUDENT_CONFIRMED_IND IN VARCHAR2,
X_END_DT IN DATE,
X_PARENT_UNIT_SET_CD IN VARCHAR2,
X_PARENT_SEQUENCE_NUMBER IN NUMBER,
X_PRIMARY_SET_IND IN VARCHAR2,
X_VOLUNTARY_END_IND IN VARCHAR2,
X_AUTHORISED_PERSON_ID IN NUMBER,
X_AUTHORISED_ON IN DATE,
X_OVERRIDE_TITLE IN VARCHAR2,
X_RQRMNTS_COMPLETE_IND IN VARCHAR2,
X_RQRMNTS_COMPLETE_DT IN DATE,
X_S_COMPLETED_SOURCE_TYPE IN VARCHAR2,
X_CATALOG_CAL_TYPE IN VARCHAR2 ,
X_CATALOG_SEQ_NUM IN NUMBER ,
X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
X_ATTRIBUTE1 IN VARCHAR2 ,
X_ATTRIBUTE2 IN VARCHAR2 ,
X_ATTRIBUTE3 IN VARCHAR2 ,
X_ATTRIBUTE4 IN VARCHAR2 ,
X_ATTRIBUTE5 IN VARCHAR2 ,
X_ATTRIBUTE6 IN VARCHAR2 ,
X_ATTRIBUTE7 IN VARCHAR2 ,
X_ATTRIBUTE8 IN VARCHAR2 ,
X_ATTRIBUTE9 IN VARCHAR2 ,
X_ATTRIBUTE10 IN VARCHAR2 ,
X_ATTRIBUTE11 IN VARCHAR2 ,
X_ATTRIBUTE12 IN VARCHAR2 ,
X_ATTRIBUTE13 IN VARCHAR2 ,
X_ATTRIBUTE14 IN VARCHAR2 ,
X_ATTRIBUTE15 IN VARCHAR2 ,
X_ATTRIBUTE16 IN VARCHAR2 ,
X_ATTRIBUTE17 IN VARCHAR2 ,
X_ATTRIBUTE18 IN VARCHAR2 ,
X_ATTRIBUTE19 IN VARCHAR2 ,
X_ATTRIBUTE20 IN VARCHAR2 ,
X_MODE IN VARCHAR2
) AS
CURSOR C IS SELECT ROWID FROM IGS_AS_SU_SETATMPT
WHERE PERSON_ID = X_PERSON_ID
AND COURSE_CD = X_COURSE_CD
AND UNIT_SET_CD = X_UNIT_SET_CD
AND SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
X_LAST_UPDATE_DATE DATE;
X_LAST_UPDATED_BY NUMBER;
X_LAST_UPDATE_LOGIN NUMBER;
X_PROGRAM_UPDATE_DATE DATE;
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;
X_PROGRAM_UPDATE_DATE := NULL;
X_PROGRAM_UPDATE_DATE := SYSDATE;
p_action=>'INSERT',
x_rowid=>X_ROWID,
x_authorised_on=>X_AUTHORISED_ON,
x_authorised_person_id=>X_AUTHORISED_PERSON_ID,
x_course_cd=>X_COURSE_CD,
x_end_dt=>X_END_DT,
x_override_title=>X_OVERRIDE_TITLE,
x_parent_sequence_number=>X_PARENT_SEQUENCE_NUMBER,
x_parent_unit_set_cd=>X_PARENT_UNIT_SET_CD,
x_person_id=>X_PERSON_ID,
x_primary_set_ind=> NVL(X_PRIMARY_SET_IND,'N'),
x_rqrmnts_complete_dt=>X_RQRMNTS_COMPLETE_DT,
x_rqrmnts_complete_ind=> NVL(X_RQRMNTS_COMPLETE_IND,'N'),
x_s_completed_source_type=>X_S_COMPLETED_SOURCE_TYPE,
x_selection_dt=>X_SELECTION_DT,
x_sequence_number=>X_SEQUENCE_NUMBER,
x_student_confirmed_ind=>X_STUDENT_CONFIRMED_IND,
x_unit_set_cd=>X_UNIT_SET_CD,
x_us_version_number=>X_US_VERSION_NUMBER,
x_voluntary_end_ind=> NVL(X_VOLUNTARY_END_IND,'N'),
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_catalog_cal_type => X_CATALOG_CAL_TYPE,
x_catalog_seq_num => X_CATALOG_SEQ_NUM,
x_attribute_category=> X_ATTRIBUTE_CATEGORY,
x_attribute1=> X_ATTRIBUTE1,
x_attribute2=> X_ATTRIBUTE2,
x_attribute3=> X_ATTRIBUTE3,
x_attribute4=> X_ATTRIBUTE4,
x_attribute5=> X_ATTRIBUTE5,
x_attribute6=> X_ATTRIBUTE6,
x_attribute7=> X_ATTRIBUTE7,
x_attribute8=> X_ATTRIBUTE8,
x_attribute9=> X_ATTRIBUTE9,
x_attribute10=>X_ATTRIBUTE10,
x_attribute11=>X_ATTRIBUTE11,
x_attribute12=>X_ATTRIBUTE12,
x_attribute13=>X_ATTRIBUTE13,
x_attribute14=>X_ATTRIBUTE14,
x_attribute15=>X_ATTRIBUTE15,
x_attribute16=>X_ATTRIBUTE16,
x_attribute17=>X_ATTRIBUTE17,
x_attribute18=>X_ATTRIBUTE18,
x_attribute19=>X_ATTRIBUTE19,
x_attribute20=>X_ATTRIBUTE20
);
INSERT INTO IGS_AS_SU_SETATMPT (
PERSON_ID,
COURSE_CD,
UNIT_SET_CD,
US_VERSION_NUMBER,
SEQUENCE_NUMBER,
SELECTION_DT,
STUDENT_CONFIRMED_IND,
END_DT,
PARENT_UNIT_SET_CD,
PARENT_SEQUENCE_NUMBER,
PRIMARY_SET_IND,
VOLUNTARY_END_IND,
AUTHORISED_PERSON_ID,
AUTHORISED_ON,
OVERRIDE_TITLE,
RQRMNTS_COMPLETE_IND,
RQRMNTS_COMPLETE_DT,
S_COMPLETED_SOURCE_TYPE,
CATALOG_CAL_TYPE,
CATALOG_SEQ_NUM,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20
) VALUES (
NEW_REFERENCES.PERSON_ID,
NEW_REFERENCES.COURSE_CD,
NEW_REFERENCES.UNIT_SET_CD,
NEW_REFERENCES.US_VERSION_NUMBER,
NEW_REFERENCES.SEQUENCE_NUMBER,
NEW_REFERENCES.SELECTION_DT,
NEW_REFERENCES.STUDENT_CONFIRMED_IND,
NEW_REFERENCES.END_DT,
NEW_REFERENCES.PARENT_UNIT_SET_CD,
NEW_REFERENCES.PARENT_SEQUENCE_NUMBER,
NEW_REFERENCES.PRIMARY_SET_IND,
NEW_REFERENCES.VOLUNTARY_END_IND,
NEW_REFERENCES.AUTHORISED_PERSON_ID,
NEW_REFERENCES.AUTHORISED_ON,
NEW_REFERENCES.OVERRIDE_TITLE,
NEW_REFERENCES.RQRMNTS_COMPLETE_IND,
NEW_REFERENCES.RQRMNTS_COMPLETE_DT,
NEW_REFERENCES.S_COMPLETED_SOURCE_TYPE,
NEW_REFERENCES.CATALOG_CAL_TYPE,
NEW_REFERENCES.CATALOG_SEQ_NUM,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_DATE,
X_LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN,
X_REQUEST_ID,
X_PROGRAM_ID,
X_PROGRAM_APPLICATION_ID,
X_PROGRAM_UPDATE_DATE,
NEW_REFERENCES.ATTRIBUTE_CATEGORY,
NEW_REFERENCES.ATTRIBUTE1,
NEW_REFERENCES.ATTRIBUTE2,
NEW_REFERENCES.ATTRIBUTE3,
NEW_REFERENCES.ATTRIBUTE4,
NEW_REFERENCES.ATTRIBUTE5,
NEW_REFERENCES.ATTRIBUTE6,
NEW_REFERENCES.ATTRIBUTE7,
NEW_REFERENCES.ATTRIBUTE8,
NEW_REFERENCES.ATTRIBUTE9,
NEW_REFERENCES.ATTRIBUTE10,
NEW_REFERENCES.ATTRIBUTE11,
NEW_REFERENCES.ATTRIBUTE12,
NEW_REFERENCES.ATTRIBUTE13,
NEW_REFERENCES.ATTRIBUTE14,
NEW_REFERENCES.ATTRIBUTE15,
NEW_REFERENCES.ATTRIBUTE16,
NEW_REFERENCES.ATTRIBUTE17,
NEW_REFERENCES.ATTRIBUTE18,
NEW_REFERENCES.ATTRIBUTE19,
NEW_REFERENCES.ATTRIBUTE20
);
p_action => 'INSERT',
x_rowid => X_ROWID
);
END INSERT_ROW;
X_SELECTION_DT IN DATE,
X_STUDENT_CONFIRMED_IND IN VARCHAR2,
X_END_DT IN DATE,
X_PARENT_UNIT_SET_CD IN VARCHAR2,
X_PARENT_SEQUENCE_NUMBER IN NUMBER,
X_PRIMARY_SET_IND IN VARCHAR2,
X_VOLUNTARY_END_IND IN VARCHAR2,
X_AUTHORISED_PERSON_ID IN NUMBER,
X_AUTHORISED_ON IN DATE,
X_OVERRIDE_TITLE IN VARCHAR2,
X_RQRMNTS_COMPLETE_IND IN VARCHAR2,
X_RQRMNTS_COMPLETE_DT IN DATE,
X_S_COMPLETED_SOURCE_TYPE IN VARCHAR2,
X_CATALOG_CAL_TYPE IN VARCHAR2 ,
X_CATALOG_SEQ_NUM IN NUMBER ,
X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
X_ATTRIBUTE1 IN VARCHAR2 ,
X_ATTRIBUTE2 IN VARCHAR2 ,
X_ATTRIBUTE3 IN VARCHAR2 ,
X_ATTRIBUTE4 IN VARCHAR2 ,
X_ATTRIBUTE5 IN VARCHAR2 ,
X_ATTRIBUTE6 IN VARCHAR2 ,
X_ATTRIBUTE7 IN VARCHAR2 ,
X_ATTRIBUTE8 IN VARCHAR2 ,
X_ATTRIBUTE9 IN VARCHAR2 ,
X_ATTRIBUTE10 IN VARCHAR2 ,
X_ATTRIBUTE11 IN VARCHAR2 ,
X_ATTRIBUTE12 IN VARCHAR2 ,
X_ATTRIBUTE13 IN VARCHAR2 ,
X_ATTRIBUTE14 IN VARCHAR2 ,
X_ATTRIBUTE15 IN VARCHAR2 ,
X_ATTRIBUTE16 IN VARCHAR2 ,
X_ATTRIBUTE17 IN VARCHAR2 ,
X_ATTRIBUTE18 IN VARCHAR2 ,
X_ATTRIBUTE19 IN VARCHAR2 ,
X_ATTRIBUTE20 IN VARCHAR2
) AS
CURSOR c1 IS SELECT
US_VERSION_NUMBER,
SELECTION_DT,
STUDENT_CONFIRMED_IND,
END_DT,
PARENT_UNIT_SET_CD,
PARENT_SEQUENCE_NUMBER,
PRIMARY_SET_IND,
VOLUNTARY_END_IND,
AUTHORISED_PERSON_ID,
AUTHORISED_ON,
OVERRIDE_TITLE,
RQRMNTS_COMPLETE_IND,
RQRMNTS_COMPLETE_DT,
S_COMPLETED_SOURCE_TYPE,
CATALOG_CAL_TYPE,
CATALOG_SEQ_NUM,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20
FROM IGS_AS_SU_SETATMPT
WHERE ROWID = X_ROWID FOR UPDATE NOWAIT;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
AND ((trunc(tlinfo.SELECTION_DT) = trunc(X_SELECTION_DT))
OR ((tlinfo.SELECTION_DT IS NULL)
AND (X_SELECTION_DT IS NULL)))
AND (tlinfo.STUDENT_CONFIRMED_IND = X_STUDENT_CONFIRMED_IND)
AND ((trunc(tlinfo.END_DT) = trunc(X_END_DT))
OR ((tlinfo.END_DT IS NULL)
AND (X_END_DT IS NULL)))
AND ((tlinfo.PARENT_UNIT_SET_CD = X_PARENT_UNIT_SET_CD)
OR ((tlinfo.PARENT_UNIT_SET_CD IS NULL)
AND (X_PARENT_UNIT_SET_CD IS NULL)))
AND ((tlinfo.PARENT_SEQUENCE_NUMBER = X_PARENT_SEQUENCE_NUMBER)
OR ((tlinfo.PARENT_SEQUENCE_NUMBER IS NULL)
AND (X_PARENT_SEQUENCE_NUMBER IS NULL)))
AND (tlinfo.PRIMARY_SET_IND = X_PRIMARY_SET_IND)
AND (tlinfo.VOLUNTARY_END_IND = X_VOLUNTARY_END_IND)
AND ((tlinfo.AUTHORISED_PERSON_ID = X_AUTHORISED_PERSON_ID)
OR ((tlinfo.AUTHORISED_PERSON_ID IS NULL)
AND (X_AUTHORISED_PERSON_ID IS NULL)))
AND ((tlinfo.AUTHORISED_ON = X_AUTHORISED_ON)
OR ((tlinfo.AUTHORISED_ON IS NULL)
AND (X_AUTHORISED_ON IS NULL)))
AND ((tlinfo.OVERRIDE_TITLE = X_OVERRIDE_TITLE)
OR ((tlinfo.OVERRIDE_TITLE IS NULL)
AND (X_OVERRIDE_TITLE IS NULL)))
AND (tlinfo.RQRMNTS_COMPLETE_IND = X_RQRMNTS_COMPLETE_IND)
AND ((trunc(tlinfo.RQRMNTS_COMPLETE_DT) = trunc(X_RQRMNTS_COMPLETE_DT))
OR ((tlinfo.RQRMNTS_COMPLETE_DT IS NULL)
AND (X_RQRMNTS_COMPLETE_DT IS NULL)))
AND ((tlinfo.S_COMPLETED_SOURCE_TYPE = X_S_COMPLETED_SOURCE_TYPE)
OR ((tlinfo.S_COMPLETED_SOURCE_TYPE IS NULL)
AND (X_S_COMPLETED_SOURCE_TYPE IS NULL)))
AND ((tlinfo.CATALOG_CAL_TYPE = X_CATALOG_CAL_TYPE)
OR ((tlinfo.CATALOG_CAL_TYPE IS NULL)
AND (X_CATALOG_CAL_TYPE IS NULL)))
AND ((tlinfo.CATALOG_SEQ_NUM = X_CATALOG_SEQ_NUM)
OR ((tlinfo.CATALOG_SEQ_NUM IS NULL)
AND (X_CATALOG_SEQ_NUM IS NULL)))
AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
OR ((tlinfo.ATTRIBUTE_CATEGORY IS NULL)
AND (X_ATTRIBUTE_CATEGORY IS NULL)))
AND ((tlinfo.ATTRIBUTE1= X_ATTRIBUTE1)
OR ((tlinfo.ATTRIBUTE1 IS NULL)
AND (X_ATTRIBUTE1 IS NULL)))
AND ((tlinfo.ATTRIBUTE2= X_ATTRIBUTE2)
OR ((tlinfo.ATTRIBUTE2 IS NULL)
AND (X_ATTRIBUTE2 IS NULL)))
AND ((tlinfo.ATTRIBUTE3= X_ATTRIBUTE3)
OR ((tlinfo.ATTRIBUTE3 IS NULL)
AND (X_ATTRIBUTE3 IS NULL)))
AND ((tlinfo.ATTRIBUTE4= X_ATTRIBUTE4)
OR ((tlinfo.ATTRIBUTE4 IS NULL)
AND (X_ATTRIBUTE4 IS NULL)))
AND ((tlinfo.ATTRIBUTE5= X_ATTRIBUTE5)
OR ((tlinfo.ATTRIBUTE5 IS NULL)
AND (X_ATTRIBUTE5 IS NULL)))
AND ((tlinfo.ATTRIBUTE6= X_ATTRIBUTE6)
OR ((tlinfo.ATTRIBUTE6 IS NULL)
AND (X_ATTRIBUTE6 IS NULL)))
AND ((tlinfo.ATTRIBUTE7= X_ATTRIBUTE7)
OR ((tlinfo.ATTRIBUTE7 IS NULL)
AND (X_ATTRIBUTE7 IS NULL)))
AND ((tlinfo.ATTRIBUTE8= X_ATTRIBUTE8)
OR ((tlinfo.ATTRIBUTE8 IS NULL)
AND (X_ATTRIBUTE8 IS NULL)))
AND ((tlinfo.ATTRIBUTE9= X_ATTRIBUTE9)
OR ((tlinfo.ATTRIBUTE9 IS NULL)
AND (X_ATTRIBUTE9 IS NULL)))
AND ((tlinfo.ATTRIBUTE10= X_ATTRIBUTE10)
OR ((tlinfo.ATTRIBUTE10 IS NULL)
AND (X_ATTRIBUTE10 IS NULL)))
AND ((tlinfo.ATTRIBUTE11= X_ATTRIBUTE11)
OR ((tlinfo.ATTRIBUTE11 IS NULL)
AND (X_ATTRIBUTE11 IS NULL)))
AND ((tlinfo.ATTRIBUTE12= X_ATTRIBUTE12)
OR ((tlinfo.ATTRIBUTE12 IS NULL)
AND (X_ATTRIBUTE12 IS NULL)))
AND ((tlinfo.ATTRIBUTE13= X_ATTRIBUTE13)
OR ((tlinfo.ATTRIBUTE13 IS NULL)
AND (X_ATTRIBUTE13 IS NULL)))
AND ((tlinfo.ATTRIBUTE14= X_ATTRIBUTE14)
OR ((tlinfo.ATTRIBUTE14 IS NULL)
AND (X_ATTRIBUTE14 IS NULL)))
AND ((tlinfo.ATTRIBUTE15= X_ATTRIBUTE15)
OR ((tlinfo.ATTRIBUTE15 IS NULL)
AND (X_ATTRIBUTE15 IS NULL)))
AND ((tlinfo.ATTRIBUTE16= X_ATTRIBUTE16)
OR ((tlinfo.ATTRIBUTE16 IS NULL)
AND (X_ATTRIBUTE16 IS NULL)))
AND ((tlinfo.ATTRIBUTE17= X_ATTRIBUTE17)
OR ((tlinfo.ATTRIBUTE17 IS NULL)
AND (X_ATTRIBUTE17 IS NULL)))
AND ((tlinfo.ATTRIBUTE18= X_ATTRIBUTE18)
OR ((tlinfo.ATTRIBUTE18 IS NULL)
AND (X_ATTRIBUTE18 IS NULL)))
AND ((tlinfo.ATTRIBUTE19= X_ATTRIBUTE19)
OR ((tlinfo.ATTRIBUTE19 IS NULL)
AND (X_ATTRIBUTE19 IS NULL)))
AND ((tlinfo.ATTRIBUTE20= X_ATTRIBUTE20)
OR ((tlinfo.ATTRIBUTE20 IS NULL)
AND (X_ATTRIBUTE20 IS NULL)))
) THEN
NULL;
PROCEDURE UPDATE_ROW (
X_ROWID IN VARCHAR2,
X_PERSON_ID IN NUMBER,
X_COURSE_CD IN VARCHAR2,
X_UNIT_SET_CD IN VARCHAR2,
X_SEQUENCE_NUMBER IN NUMBER,
X_US_VERSION_NUMBER IN NUMBER,
X_SELECTION_DT IN DATE,
X_STUDENT_CONFIRMED_IND IN VARCHAR2,
X_END_DT IN DATE,
X_PARENT_UNIT_SET_CD IN VARCHAR2,
X_PARENT_SEQUENCE_NUMBER IN NUMBER,
X_PRIMARY_SET_IND IN VARCHAR2,
X_VOLUNTARY_END_IND IN VARCHAR2,
X_AUTHORISED_PERSON_ID IN NUMBER,
X_AUTHORISED_ON IN DATE,
X_OVERRIDE_TITLE IN VARCHAR2,
X_RQRMNTS_COMPLETE_IND IN VARCHAR2,
X_RQRMNTS_COMPLETE_DT IN DATE,
X_S_COMPLETED_SOURCE_TYPE IN VARCHAR2,
X_CATALOG_CAL_TYPE IN VARCHAR2 ,
X_CATALOG_SEQ_NUM IN NUMBER ,
X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
X_ATTRIBUTE1 IN VARCHAR2 ,
X_ATTRIBUTE2 IN VARCHAR2 ,
X_ATTRIBUTE3 IN VARCHAR2 ,
X_ATTRIBUTE4 IN VARCHAR2 ,
X_ATTRIBUTE5 IN VARCHAR2 ,
X_ATTRIBUTE6 IN VARCHAR2 ,
X_ATTRIBUTE7 IN VARCHAR2 ,
X_ATTRIBUTE8 IN VARCHAR2 ,
X_ATTRIBUTE9 IN VARCHAR2 ,
X_ATTRIBUTE10 IN VARCHAR2 ,
X_ATTRIBUTE11 IN VARCHAR2 ,
X_ATTRIBUTE12 IN VARCHAR2 ,
X_ATTRIBUTE13 IN VARCHAR2 ,
X_ATTRIBUTE14 IN VARCHAR2 ,
X_ATTRIBUTE15 IN VARCHAR2 ,
X_ATTRIBUTE16 IN VARCHAR2 ,
X_ATTRIBUTE17 IN VARCHAR2 ,
X_ATTRIBUTE18 IN VARCHAR2 ,
X_ATTRIBUTE19 IN VARCHAR2 ,
X_ATTRIBUTE20 IN VARCHAR2 ,
X_MODE IN VARCHAR2
) AS
X_LAST_UPDATE_DATE DATE;
X_LAST_UPDATED_BY NUMBER;
X_LAST_UPDATE_LOGIN NUMBER;
X_PROGRAM_UPDATE_DATE DATE;
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_authorised_on=>X_AUTHORISED_ON,
x_authorised_person_id=>X_AUTHORISED_PERSON_ID,
x_course_cd=>X_COURSE_CD,
x_end_dt=>X_END_DT,
x_override_title=>X_OVERRIDE_TITLE,
x_parent_sequence_number=>X_PARENT_SEQUENCE_NUMBER,
x_parent_unit_set_cd=>X_PARENT_UNIT_SET_CD,
x_person_id=>X_PERSON_ID,
x_primary_set_ind=>X_PRIMARY_SET_IND,
x_rqrmnts_complete_dt=>X_RQRMNTS_COMPLETE_DT,
x_rqrmnts_complete_ind=>X_RQRMNTS_COMPLETE_IND,
x_s_completed_source_type=>X_S_COMPLETED_SOURCE_TYPE,
x_selection_dt=>X_SELECTION_DT,
x_sequence_number=>X_SEQUENCE_NUMBER,
x_student_confirmed_ind=>X_STUDENT_CONFIRMED_IND,
x_unit_set_cd=>X_UNIT_SET_CD,
x_us_version_number=>X_US_VERSION_NUMBER,
x_voluntary_end_ind=>X_VOLUNTARY_END_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_catalog_cal_type => X_CATALOG_CAL_TYPE,
x_catalog_seq_num => X_CATALOG_SEQ_NUM,
x_attribute_category=> X_ATTRIBUTE_CATEGORY,
x_attribute1=> X_ATTRIBUTE1,
x_attribute2=> X_ATTRIBUTE2,
x_attribute3=> X_ATTRIBUTE3,
x_attribute4=> X_ATTRIBUTE4,
x_attribute5=> X_ATTRIBUTE5,
x_attribute6=> X_ATTRIBUTE6,
x_attribute7=> X_ATTRIBUTE7,
x_attribute8=> X_ATTRIBUTE8,
x_attribute9=> X_ATTRIBUTE9,
x_attribute10=>X_ATTRIBUTE10,
x_attribute11=>X_ATTRIBUTE11,
x_attribute12=>X_ATTRIBUTE12,
x_attribute13=>X_ATTRIBUTE13,
x_attribute14=>X_ATTRIBUTE14,
x_attribute15=>X_ATTRIBUTE15,
x_attribute16=>X_ATTRIBUTE16,
x_attribute17=>X_ATTRIBUTE17,
x_attribute18=>X_ATTRIBUTE18,
x_attribute19=>X_ATTRIBUTE19,
x_attribute20=>X_ATTRIBUTE20
);
X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
X_PROGRAM_UPDATE_DATE := SYSDATE;
UPDATE IGS_AS_SU_SETATMPT SET
US_VERSION_NUMBER = NEW_REFERENCES.US_VERSION_NUMBER,
SELECTION_DT = NEW_REFERENCES.SELECTION_DT,
STUDENT_CONFIRMED_IND = NEW_REFERENCES.STUDENT_CONFIRMED_IND,
END_DT = NEW_REFERENCES.END_DT,
PARENT_UNIT_SET_CD = NEW_REFERENCES.PARENT_UNIT_SET_CD,
PARENT_SEQUENCE_NUMBER = NEW_REFERENCES.PARENT_SEQUENCE_NUMBER,
PRIMARY_SET_IND = NEW_REFERENCES.PRIMARY_SET_IND,
VOLUNTARY_END_IND = NEW_REFERENCES.VOLUNTARY_END_IND,
AUTHORISED_PERSON_ID = NEW_REFERENCES.AUTHORISED_PERSON_ID,
AUTHORISED_ON = NEW_REFERENCES.AUTHORISED_ON,
OVERRIDE_TITLE = NEW_REFERENCES.OVERRIDE_TITLE,
RQRMNTS_COMPLETE_IND = NEW_REFERENCES.RQRMNTS_COMPLETE_IND,
RQRMNTS_COMPLETE_DT = NEW_REFERENCES.RQRMNTS_COMPLETE_DT,
S_COMPLETED_SOURCE_TYPE = NEW_REFERENCES.S_COMPLETED_SOURCE_TYPE,
CATALOG_CAL_TYPE = NEW_REFERENCES.CATALOG_CAL_TYPE,
CATALOG_SEQ_NUM = NEW_REFERENCES.CATALOG_SEQ_NUM,
LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
LAST_UPDATED_BY = X_LAST_UPDATED_BY,
LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
REQUEST_ID = X_REQUEST_ID,
PROGRAM_ID = X_PROGRAM_ID,
PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
ATTRIBUTE_CATEGORY=X_ATTRIBUTE_CATEGORY,
ATTRIBUTE1=NEW_REFERENCES.ATTRIBUTE1,
ATTRIBUTE2=NEW_REFERENCES.ATTRIBUTE2,
ATTRIBUTE3=NEW_REFERENCES.ATTRIBUTE3,
ATTRIBUTE4=NEW_REFERENCES.ATTRIBUTE4,
ATTRIBUTE5=NEW_REFERENCES.ATTRIBUTE5,
ATTRIBUTE6=NEW_REFERENCES.ATTRIBUTE6,
ATTRIBUTE7=NEW_REFERENCES.ATTRIBUTE7,
ATTRIBUTE8=NEW_REFERENCES.ATTRIBUTE8,
ATTRIBUTE9=NEW_REFERENCES.ATTRIBUTE9,
ATTRIBUTE10=NEW_REFERENCES.ATTRIBUTE10,
ATTRIBUTE11=NEW_REFERENCES.ATTRIBUTE11,
ATTRIBUTE12=NEW_REFERENCES.ATTRIBUTE12,
ATTRIBUTE13=NEW_REFERENCES.ATTRIBUTE13,
ATTRIBUTE14=NEW_REFERENCES.ATTRIBUTE14,
ATTRIBUTE15=NEW_REFERENCES.ATTRIBUTE15,
ATTRIBUTE16=NEW_REFERENCES.ATTRIBUTE16,
ATTRIBUTE17=NEW_REFERENCES.ATTRIBUTE17,
ATTRIBUTE18=NEW_REFERENCES.ATTRIBUTE18,
ATTRIBUTE19=NEW_REFERENCES.ATTRIBUTE19,
ATTRIBUTE20=NEW_REFERENCES.ATTRIBUTE20
WHERE ROWID = X_ROWID;
p_action => 'UPDATE',
x_rowid => X_ROWID
);
END UPDATE_ROW;
X_SELECTION_DT IN DATE,
X_STUDENT_CONFIRMED_IND IN VARCHAR2,
X_END_DT IN DATE,
X_PARENT_UNIT_SET_CD IN VARCHAR2,
X_PARENT_SEQUENCE_NUMBER IN NUMBER,
X_PRIMARY_SET_IND IN VARCHAR2,
X_VOLUNTARY_END_IND IN VARCHAR2,
X_AUTHORISED_PERSON_ID IN NUMBER,
X_AUTHORISED_ON IN DATE,
X_OVERRIDE_TITLE IN VARCHAR2,
X_RQRMNTS_COMPLETE_IND IN VARCHAR2,
X_RQRMNTS_COMPLETE_DT IN DATE,
X_S_COMPLETED_SOURCE_TYPE IN VARCHAR2,
X_CATALOG_CAL_TYPE IN VARCHAR2 ,
X_CATALOG_SEQ_NUM IN NUMBER ,
X_ATTRIBUTE_CATEGORY IN VARCHAR2 ,
X_ATTRIBUTE1 IN VARCHAR2 ,
X_ATTRIBUTE2 IN VARCHAR2 ,
X_ATTRIBUTE3 IN VARCHAR2 ,
X_ATTRIBUTE4 IN VARCHAR2 ,
X_ATTRIBUTE5 IN VARCHAR2 ,
X_ATTRIBUTE6 IN VARCHAR2 ,
X_ATTRIBUTE7 IN VARCHAR2 ,
X_ATTRIBUTE8 IN VARCHAR2 ,
X_ATTRIBUTE9 IN VARCHAR2 ,
X_ATTRIBUTE10 IN VARCHAR2 ,
X_ATTRIBUTE11 IN VARCHAR2 ,
X_ATTRIBUTE12 IN VARCHAR2 ,
X_ATTRIBUTE13 IN VARCHAR2 ,
X_ATTRIBUTE14 IN VARCHAR2 ,
X_ATTRIBUTE15 IN VARCHAR2 ,
X_ATTRIBUTE16 IN VARCHAR2 ,
X_ATTRIBUTE17 IN VARCHAR2 ,
X_ATTRIBUTE18 IN VARCHAR2 ,
X_ATTRIBUTE19 IN VARCHAR2 ,
X_ATTRIBUTE20 IN VARCHAR2 ,
X_MODE IN VARCHAR2
) AS
CURSOR c1 IS SELECT ROWID FROM IGS_AS_SU_SETATMPT
WHERE PERSON_ID = X_PERSON_ID
AND COURSE_CD = X_COURSE_CD
AND UNIT_SET_CD = X_UNIT_SET_CD
AND SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
;
INSERT_ROW (
X_ROWID,
X_PERSON_ID,
X_COURSE_CD,
X_UNIT_SET_CD,
X_SEQUENCE_NUMBER,
X_US_VERSION_NUMBER,
X_SELECTION_DT,
X_STUDENT_CONFIRMED_IND,
X_END_DT,
X_PARENT_UNIT_SET_CD,
X_PARENT_SEQUENCE_NUMBER,
X_PRIMARY_SET_IND,
X_VOLUNTARY_END_IND,
X_AUTHORISED_PERSON_ID,
X_AUTHORISED_ON,
X_OVERRIDE_TITLE,
X_RQRMNTS_COMPLETE_IND,
X_RQRMNTS_COMPLETE_DT,
X_S_COMPLETED_SOURCE_TYPE,
X_CATALOG_CAL_TYPE,
X_CATALOG_SEQ_NUM,
X_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1,
X_ATTRIBUTE2,
X_ATTRIBUTE3,
X_ATTRIBUTE4,
X_ATTRIBUTE5,
X_ATTRIBUTE6,
X_ATTRIBUTE7,
X_ATTRIBUTE8,
X_ATTRIBUTE9,
X_ATTRIBUTE10,
X_ATTRIBUTE11,
X_ATTRIBUTE12,
X_ATTRIBUTE13,
X_ATTRIBUTE14,
X_ATTRIBUTE15,
X_ATTRIBUTE16,
X_ATTRIBUTE17,
X_ATTRIBUTE18,
X_ATTRIBUTE19,
X_ATTRIBUTE20,
X_MODE);
UPDATE_ROW (
X_ROWID,
X_PERSON_ID,
X_COURSE_CD,
X_UNIT_SET_CD,
X_SEQUENCE_NUMBER,
X_US_VERSION_NUMBER,
X_SELECTION_DT,
X_STUDENT_CONFIRMED_IND,
X_END_DT,
X_PARENT_UNIT_SET_CD,
X_PARENT_SEQUENCE_NUMBER,
X_PRIMARY_SET_IND,
X_VOLUNTARY_END_IND,
X_AUTHORISED_PERSON_ID,
X_AUTHORISED_ON,
X_OVERRIDE_TITLE,
X_RQRMNTS_COMPLETE_IND,
X_RQRMNTS_COMPLETE_DT,
X_S_COMPLETED_SOURCE_TYPE,
X_CATALOG_CAL_TYPE,
X_CATALOG_SEQ_NUM,
X_ATTRIBUTE_CATEGORY,
X_ATTRIBUTE1,
X_ATTRIBUTE2,
X_ATTRIBUTE3,
X_ATTRIBUTE4,
X_ATTRIBUTE5,
X_ATTRIBUTE6,
X_ATTRIBUTE7,
X_ATTRIBUTE8,
X_ATTRIBUTE9,
X_ATTRIBUTE10,
X_ATTRIBUTE11,
X_ATTRIBUTE12,
X_ATTRIBUTE13,
X_ATTRIBUTE14,
X_ATTRIBUTE15,
X_ATTRIBUTE16,
X_ATTRIBUTE17,
X_ATTRIBUTE18,
X_ATTRIBUTE19,
X_ATTRIBUTE20,
X_MODE);
PROCEDURE DELETE_ROW (
X_ROWID IN VARCHAR2,
x_mode IN VARCHAR2) AS
BEGIN
--
Before_DML(
p_action => 'DELETE',
x_rowid => X_ROWID
);
DELETE FROM IGS_AS_SU_SETATMPT
WHERE ROWID = X_ROWID;
p_action => 'DELETE',
x_rowid => X_ROWID
);
END DELETE_ROW;