The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT birth_date FROM igs_pe_person_base_v
WHERE person_id = cp_person_id;
SELECT 1
FROM hz_contact_preferences
WHERE contact_level_table_id = cp_person_id
AND contact_type = 'VISIT'
AND ( cp_cpid IS NULL OR contact_preference_id <> cp_cpid)
AND (NVL(preference_END_DATE,TO_DATE('4712/12/31','YYYY/MM/DD')) >= cp_start_date OR
NVL(preference_END_DATE,TO_DATE('4712/12/31','YYYY/MM/DD')) >= cp_end_date)
AND (preference_START_DATE <= cp_start_date OR preference_START_DATE <= cp_end_date);
procedure INSERT_ROW (
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_ROWID in out NOCOPY VARCHAR2,
x_CONTACT_PREFERENCE_ID IN OUT NOCOPY NUMBER,
x_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
X_CONTACT_LEVEL_TABLE IN VARCHAR2,
X_CONTACT_LEVEL_TABLE_ID IN NUMBER,
X_CONTACT_TYPE IN VARCHAR2,
X_PREFERENCE_CODE IN VARCHAR2,
X_PREFERENCE_START_DATE IN DATE,
X_PREFERENCE_END_DATE IN DATE,
X_REQUESTED_BY IN VARCHAR2,
X_REASON_CODE IN VARCHAR2,
X_STATUS IN VARCHAR2,
X_MODE in VARCHAR2 default 'R'
) AS
l_cont_pref_rec_type HZ_CONTACT_PREFERENCE_V2PUB.contact_preference_rec_type;
-- after successful insert, pass OVN out as 1.
x_OBJECT_VERSION_NUMBER :=1;
END INSERT_ROW;
procedure UPDATE_ROW (
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_ROWID in out NOCOPY VARCHAR2,
x_CONTACT_PREFERENCE_ID IN OUT NOCOPY NUMBER,
x_OBJECT_VERSION_NUMBER IN OUT NOCOPY NUMBER,
X_CONTACT_LEVEL_TABLE IN VARCHAR2,
X_CONTACT_LEVEL_TABLE_ID IN NUMBER,
X_CONTACT_TYPE IN VARCHAR2,
X_PREFERENCE_CODE IN VARCHAR2,
X_PREFERENCE_START_DATE IN DATE,
X_PREFERENCE_END_DATE IN DATE,
X_REQUESTED_BY IN VARCHAR2,
X_REASON_CODE IN VARCHAR2,
X_STATUS IN VARCHAR2,
X_MODE in VARCHAR2 default 'R'
) AS
l_cont_pref_rec_type HZ_CONTACT_PREFERENCE_V2PUB.contact_preference_rec_type;
HZ_CONTACT_PREFERENCE_V2PUB.update_contact_preference (
p_init_msg_list => l_init_msg_list,
p_contact_preference_rec => l_cont_pref_rec_type,
p_object_version_number => x_object_version_number,
x_return_status => x_return_status,
x_msg_count => x_msg_count ,
x_msg_data => x_msg_data );
END UPDATE_ROW;