[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
) AS
/*
|| Created By :
|| Created On : 28-APR-2003
|| Purpose : Initialises the Old and New references for the columns of the table.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
CURSOR cur_old_ref_values IS
SELECT *
FROM igs_pe_hz_rel
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;
PROCEDURE AfterRowInsertUpdate(
p_rowid IN ROWID,
p_inserting IN BOOLEAN,
p_updating IN BOOLEAN,
p_deleting IN BOOLEAN
) as
------------------------------------------------------------------------------------------
--Created by : kpadiyar
--Date created: 10-JAN-2003
--
--Purpose: To form the Joint Salutation.
--
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
--asbala 15-JAN-2004 3349171: Incorrect usage of fnd_lookup_values view
----------------------------------------------------------------------------------------------
--
-- cursor to get the surname, prefix, given_names of member
--
CURSOR check_update IS
SELECT d.subject_id
FROM igs_pe_hz_rel a , hz_relationships d
WHERE a.rowid = p_rowid
AND (NVL(a.primary,'N') = 'Y' OR NVL(a.secondary,'N') = 'Y')
AND d.relationship_id = a.relationship_id
AND a.directional_flag = d.directional_flag;
SELECT a.rowid,
b.person_last_name surname,
b.person_first_name given_names,
b.person_pre_name_adjunct prefix
FROM igs_pe_hz_rel a ,hz_parties b , hz_relationships c
WHERE a.relationship_id = c.relationship_id
AND c.subject_id = p_person_id
AND c.object_id = b.party_id
AND c.directional_flag = a.directional_flag
AND NVL(a.primary,'N') = 'Y'
AND rownum = 1;
SELECT a.rowid,
b.person_last_name surname,
b.person_first_name given_names,
b.person_pre_name_adjunct prefix
FROM igs_pe_hz_rel a ,hz_parties b , hz_relationships c
WHERE a.relationship_id = c.relationship_id
AND c.subject_id = p_person_id
AND c.object_id = b.party_id
AND c.directional_flag = a.directional_flag
AND NVL(a.secondary,'N') = 'Y'
AND rownum = 1;
SELECT meaning
FROM fnd_lookup_values
WHERE lookup_type = p_lookup_type
AND view_application_id = p_view_application_id
AND language = USERENV('LANG')
AND security_group_id = p_security_group_id
AND lookup_code = p_prefix
AND enabled_flag = 'Y';
lv_update VARCHAR2(1);
lv_update_joint_sal VARCHAR2(1);
l_check_update check_update%ROWTYPE;
lv_update := 'N';
lv_update_joint_sal := 'N';
IF p_inserting OR p_updating THEN
IF p_updating THEN
OPEN check_update;
FETCH check_update INTO l_check_update;
IF check_update%FOUND THEN
IF new_references.primary = 'Y' THEN
IF ( NVL(old_references.joint_salutation,'N') <> NVL(new_references.joint_salutation,'N')) THEN
--
-- fetch secondary member details
--
OPEN c_member_detail2 (l_check_update.subject_id);
UPDATE igs_pe_hz_rel
SET
joint_salutation = new_references.joint_salutation
WHERE rowid =rec_sec_member_detail.rowid;
OPEN c_member_detail1 (l_check_update.subject_id);
UPDATE igs_pe_hz_rel
SET
joint_salutation = new_references.joint_salutation
WHERE rowid = rec_prime_member_detail.rowid;
CLOSE check_update;
UPDATE igs_pe_hz_rel
SET joint_salutation = NULL
WHERE rowid = p_rowid;
OPEN check_update;
FETCH check_update INTO l_check_update;
IF check_update%FOUND THEN
IF
( NVL(old_references.primary,'N') <> NVL(new_references.primary,'N')) OR
( NVL(old_references.secondary,'N') <> NVL(new_references.secondary,'N'))
THEN
lv_update := 'Y';
lv_update := 'N';
CLOSE check_update;
OPEN check_update;
FETCH check_update INTO l_check_update;
IF check_update%FOUND THEN
lv_update := 'Y';
lv_update := 'N';
CLOSE check_update;
IF lv_update = 'Y' THEN
-- fetch primary member details
--
OPEN c_member_detail1 (l_check_update.subject_id);
OPEN c_member_detail2 (l_check_update.subject_id);
UPDATE igs_pe_hz_rel
SET
joint_salutation = trim(lv_joint_salutation)
WHERE rowid in (rec_prime_member_detail.rowid,rec_sec_member_detail.rowid);
END AfterRowInsertUpdate;
PROCEDURE before_row_insert_update AS
/*
|| Created By :
|| Created On : 28-APR-2003
|| Purpose : Checks for the existance of Parent records.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
BEGIN
IF new_references.primary = 'Y' AND new_references.secondary = 'Y' THEN
fnd_message.set_name('IGS', 'IGS_AD_NOT_BOTH_PRIM_SEC');
END before_row_insert_update;
SELECT 'Y' FROM HZ_RELATIONSHIPS
WHERE relationship_id = new_references.relationship_id AND
directional_flag = new_references.directional_flag;
fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
SELECT rowid
FROM igs_pe_hz_rel
WHERE relationship_id = x_relationship_id
AND directional_flag = x_directional_flag
FOR UPDATE NOWAIT;
x_last_update_date IN DATE,
x_last_updated_by IN NUMBER,
x_last_update_login IN NUMBER
) AS
/*
|| Created By :
|| Created On : 28-APR-2003
|| Purpose : Initialises the columns, Checks Constraints, Calls the
|| Trigger Handlers for the table, before any DML operation.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
BEGIN
set_column_values (
p_action,
x_rowid,
x_relationship_id,
x_directional_flag,
x_primary,
x_secondary,
x_joint_salutation,
x_next_to_kin,
x_rep_faculty,
x_rep_staff,
x_rep_student,
x_rep_alumni,
x_emergency_contact_flag,
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.
IF ( get_pk_for_validation(
new_references.relationship_id,
new_references.directional_flag
)
) THEN
fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
before_row_insert_update;
ELSIF (p_action = 'UPDATE') THEN
-- Call all the procedures related to Before Update.
check_parent_existance;
before_row_insert_update;
ELSIF (p_action = 'VALIDATE_INSERT') THEN
-- Call all the procedures related to Before Insert.
IF ( get_pk_for_validation (
new_references.relationship_id,
new_references.directional_flag
)
) THEN
fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
before_row_insert_update;
ELSIF (p_action = 'VALIDATE_UPDATE') THEN
before_row_insert_update;
IF (p_action = 'INSERT') THEN
-- Call all the procedures related to After Insert.
AfterRowInsertUpdate(
p_rowid => l_rowid,
p_inserting => TRUE,
p_updating => FALSE,
p_deleting => FALSE
);
ELSIF (p_action = 'UPDATE') THEN
-- Call all the procedures related to After Update.
AfterRowInsertUpdate(
p_rowid => l_rowid,
p_inserting => FALSE,
p_updating => TRUE,
p_deleting => FALSE
);
PROCEDURE insert_row (
x_rowid IN OUT NOCOPY VARCHAR2,
x_relationship_id IN NUMBER,
x_directional_flag IN VARCHAR2,
x_primary IN VARCHAR2,
x_secondary IN VARCHAR2,
x_joint_salutation IN VARCHAR2,
x_next_to_kin IN VARCHAR2,
x_rep_faculty IN VARCHAR2,
x_rep_staff IN VARCHAR2,
x_rep_student IN VARCHAR2,
x_rep_alumni IN VARCHAR2,
x_emergency_contact_flag IN VARCHAR2,
x_mode IN VARCHAR2
) AS
/*
|| Created By :
|| Created On : 28-APR-2003
|| Purpose : Handles the INSERT DML logic for the table.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
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;
fnd_message.set_token ('ROUTINE', 'IGS_PE_HZ_REL_PKG.INSERT_ROW');
p_action => 'INSERT',
x_rowid => x_rowid,
x_relationship_id => x_relationship_id,
x_directional_flag => x_directional_flag,
x_primary => x_primary,
x_secondary => x_secondary,
x_joint_salutation => x_joint_salutation,
x_next_to_kin => x_next_to_kin,
x_rep_faculty => x_rep_faculty,
x_rep_staff => x_rep_staff,
x_rep_student => x_rep_student,
x_rep_alumni => x_rep_alumni,
x_emergency_contact_flag => x_emergency_contact_flag,
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
);
INSERT INTO igs_pe_hz_rel (
relationship_id,
directional_flag,
primary,
secondary,
joint_salutation,
next_to_kin,
rep_faculty,
rep_staff,
rep_student,
rep_alumni,
emergency_contact_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
) VALUES (
new_references.relationship_id,
new_references.directional_flag,
new_references.primary,
new_references.secondary,
new_references.joint_salutation,
new_references.next_to_kin,
new_references.rep_faculty,
new_references.rep_staff,
new_references.rep_student,
new_references.rep_alumni,
new_references.emergency_contact_flag,
x_last_update_date,
x_last_updated_by,
x_last_update_date,
x_last_updated_by,
x_last_update_login
) RETURNING ROWID INTO x_rowid;
INSERT INTO igs_pe_hz_rel (
relationship_id,
directional_flag,
primary,
secondary,
next_to_kin,
rep_faculty,
rep_staff,
rep_student,
rep_alumni,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
) VALUES (
new_references.relationship_id,
l_directional_flag,
'N',
'N',
'N',
'N',
'N',
'N',
'N',
x_last_update_date,
x_last_updated_by,
x_last_update_date,
x_last_updated_by,
x_last_update_login
);
after_dml(p_action => 'INSERT',x_rowid => x_rowid);
END insert_row;
SELECT
primary,
secondary,
joint_salutation,
next_to_kin,
rep_faculty,
rep_staff,
rep_student,
rep_alumni,
emergency_contact_flag
FROM igs_pe_hz_rel
WHERE rowid = x_rowid
FOR UPDATE NOWAIT;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
PROCEDURE update_row (
x_rowid IN VARCHAR2,
x_relationship_id IN NUMBER,
x_directional_flag IN VARCHAR2,
x_primary IN VARCHAR2,
x_secondary IN VARCHAR2,
x_joint_salutation IN VARCHAR2,
x_next_to_kin IN VARCHAR2,
x_rep_faculty IN VARCHAR2,
x_rep_staff IN VARCHAR2,
x_rep_student IN VARCHAR2,
x_rep_alumni IN VARCHAR2,
x_emergency_contact_flag IN VARCHAR2,
x_mode IN VARCHAR2
) AS
/*
|| Created By :
|| Created On : 28-APR-2003
|| Purpose : Handles the UPDATE DML logic for the table.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
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;
fnd_message.set_token ('ROUTINE', 'IGS_PE_HZ_REL_PKG.UPDATE_ROW');
p_action => 'UPDATE',
x_rowid => x_rowid,
x_relationship_id => x_relationship_id,
x_directional_flag => x_directional_flag,
x_primary => x_primary,
x_secondary => x_secondary,
x_joint_salutation => x_joint_salutation,
x_next_to_kin => x_next_to_kin,
x_rep_faculty => x_rep_faculty,
x_rep_staff => x_rep_staff,
x_rep_student => x_rep_student,
x_rep_alumni => x_rep_alumni,
x_emergency_contact_flag => x_emergency_contact_flag,
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
);
UPDATE igs_pe_hz_rel
SET
primary = new_references.primary,
secondary = new_references.secondary,
joint_salutation = new_references.joint_salutation,
next_to_kin = new_references.next_to_kin,
rep_faculty = new_references.rep_faculty,
rep_staff = new_references.rep_staff,
rep_student = new_references.rep_student,
rep_alumni = new_references.rep_alumni,
emergency_contact_flag = new_references.emergency_contact_flag,
last_update_date = x_last_update_date,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login
WHERE rowid = x_rowid;
UPDATE igs_pe_hz_rel
SET
primary = new_references.primary,
secondary = new_references.secondary,
joint_salutation = new_references.joint_salutation,
next_to_kin = new_references.next_to_kin,
rep_faculty = new_references.rep_faculty,
rep_staff = new_references.rep_staff,
rep_student = new_references.rep_student,
rep_alumni = new_references.rep_alumni,
emergency_contact_flag = old_references.emergency_contact_flag,
last_update_date = x_last_update_date,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login
WHERE rowid = x_rowid;
after_dml(p_action => 'UPDATE', x_rowid => x_rowid);
END update_row;
|| Purpose : Adds a row if there is no existing row, otherwise updates existing row in the table.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
CURSOR c1 IS
SELECT rowid
FROM igs_pe_hz_rel
WHERE relationship_id = x_relationship_id
AND directional_flag = x_directional_flag;
insert_row (
x_rowid,
x_relationship_id,
x_directional_flag,
x_primary,
x_secondary,
x_joint_salutation,
x_next_to_kin,
x_rep_faculty,
x_rep_staff,
x_rep_student,
x_rep_alumni,
x_emergency_contact_flag,
x_mode
);
update_row (
x_rowid,
x_relationship_id,
x_directional_flag,
x_primary,
x_secondary,
x_joint_salutation,
x_next_to_kin,
x_rep_faculty,
x_rep_staff,
x_rep_student,
x_rep_alumni,
x_emergency_contact_flag,
x_mode
);
PROCEDURE delete_row (
x_rowid IN VARCHAR2,
x_mode IN VARCHAR2
) AS
/*
|| Created By :
|| Created On : 28-APR-2003
|| Purpose : Handles the DELETE DML logic for the table.
|| Known limitations, enhancements or remarks :
|| Change History :
|| Who When What
|| (reverse chronological order - newest change first)
*/
BEGIN
before_dml (
p_action => 'DELETE',
x_rowid => x_rowid
);
DELETE FROM igs_pe_hz_rel
WHERE rowid = x_rowid;
END delete_row;