[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_last_update_date IN DATE , -- DEFAULT NULL,
x_last_updated_by IN NUMBER , -- DEFAULT NULL,
x_last_update_login IN NUMBER -- DEFAULT NULL
) AS
/*
|| Created By : cdcruz
|| Created On : 21-SEP-2001
|| 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_immu_dtls
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 BeforeRowInsertUpdate(
p_inserting IN BOOLEAN,
p_updating IN BOOLEAN,
p_deleting IN BOOLEAN
) as
------------------------------------------------------------------------------------------
--Created by : vredkar
--Date created: 19-JUL-2005
--
--Purpose:
--Known limitations/enhancements and/or remarks:
--
--Change History:
--Who When What
----------------------------------------------------------------------------------------------
CURSOR validate_brth_dt(cp_person_id NUMBER) IS
SELECT birth_date FROM
IGS_PE_PERSON_BASE_V
WHERE person_id = cp_person_id ;
IF p_inserting OR p_updating THEN
OPEN validate_brth_dt(new_references.person_id);
END BeforeRowInsertUpdate;
fnd_message.set_name ('FND', 'FORM_RECORD_DELETED');
SELECT rowid
FROM igs_pe_immu_dtls
WHERE immu_details_id = x_immu_details_id
FOR UPDATE NOWAIT;
SELECT rowid
FROM igs_pe_immu_dtls
WHERE person_id = x_person_id
AND immunization_code = x_immunization_code
AND start_date = x_start_date
AND ((l_rowid IS NULL) OR (rowid <> l_rowid));
SELECT rowid
FROM igs_pe_immu_dtls
WHERE ((person_id = x_party_id));
x_last_update_date IN DATE ,
x_last_updated_by IN NUMBER,
x_last_update_login IN NUMBER
) AS
/*
|| Created By : cdcruz
|| Created On : 21-SEP-2001
|| 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_immu_details_id,
x_person_id,
x_immunization_code,
x_status_code,
x_start_date,
x_end_date,
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.
BeforeRowInsertUpdate( TRUE, FALSE,FALSE );
ELSIF (p_action = 'UPDATE') THEN
-- Call all the procedures related to Before Update.
BeforeRowInsertUpdate( FALSE,TRUE,FALSE );
ELSIF (p_action = 'VALIDATE_INSERT') THEN
-- Call all the procedures related to Before Insert.
IF ( get_pk_for_validation (
new_references.immu_details_id
)
) THEN
fnd_message.set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
ELSIF (p_action = 'VALIDATE_UPDATE') THEN
check_uniqueness;
PROCEDURE insert_row (
x_rowid IN OUT NOCOPY VARCHAR2,
x_immu_details_id IN OUT NOCOPY NUMBER,
x_person_id IN NUMBER,
x_immunization_code IN VARCHAR2,
x_status_code IN VARCHAR2,
x_start_date IN DATE,
x_end_date IN DATE,
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 -- DEFAULT 'R'
) AS
/*
|| Created By : cdcruz
|| Created On : 21-SEP-2001
|| 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)
*/
CURSOR c IS
SELECT rowid
FROM igs_pe_immu_dtls
WHERE immu_details_id = x_immu_details_id;
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;
SELECT igs_pe_immu_dtls_s.NEXTVAL
INTO x_immu_details_id
FROM dual;
p_action => 'INSERT',
x_rowid => x_rowid,
x_immu_details_id => x_immu_details_id,
x_person_id => x_person_id,
x_immunization_code => x_immunization_code,
x_status_code => x_status_code,
x_start_date => x_start_date,
x_end_date => x_end_date,
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_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_immu_dtls (
immu_details_id,
person_id,
immunization_code,
status_code,
start_date,
end_date,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
) VALUES (
new_references.immu_details_id,
new_references.person_id,
new_references.immunization_code,
new_references.status_code,
new_references.start_date,
new_references.end_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,
x_last_update_date,
x_last_updated_by,
x_last_update_date,
x_last_updated_by,
x_last_update_login
);
END insert_row;
SELECT
person_id,
immunization_code,
status_code,
start_date,
end_date,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ATTRIBUTE16,
ATTRIBUTE17,
ATTRIBUTE18,
ATTRIBUTE19,
ATTRIBUTE20
FROM igs_pe_immu_dtls
WHERE rowid = x_rowid
FOR UPDATE NOWAIT;
fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
PROCEDURE update_row (
x_rowid IN VARCHAR2,
x_immu_details_id IN NUMBER,
x_person_id IN NUMBER,
x_immunization_code IN VARCHAR2,
x_status_code IN VARCHAR2,
x_start_date IN DATE,
x_end_date IN DATE,
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 -- DEFAULT 'R'
) AS
/*
|| Created By : cdcruz
|| Created On : 21-SEP-2001
|| 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;
p_action => 'UPDATE',
x_rowid => x_rowid,
x_immu_details_id => x_immu_details_id,
x_person_id => x_person_id,
x_immunization_code => x_immunization_code,
x_status_code => x_status_code,
x_start_date => x_start_date,
x_end_date => x_end_date,
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_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_immu_dtls
SET
person_id = new_references.person_id,
immunization_code = new_references.immunization_code,
status_code = new_references.status_code,
start_date = new_references.start_date,
end_date = new_references.end_date,
ATTRIBUTE_CATEGORY = NEW_REFERENCES.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,
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;
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_immu_dtls
WHERE immu_details_id = x_immu_details_id;
insert_row (
x_rowid,
x_immu_details_id,
x_person_id,
x_immunization_code,
x_status_code,
x_start_date,
x_end_date,
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_immu_details_id,
x_person_id,
x_immunization_code,
x_status_code,
x_start_date,
x_end_date,
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
/*
|| Created By : cdcruz
|| Created On : 21-SEP-2001
|| 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_immu_dtls
WHERE rowid = x_rowid;
END delete_row;