[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE preference_date_nonupdateable(
p_column IN VARCHAR2,
p_column_value IN DATE,
p_old_column_value IN DATE,
p_restricted IN VARCHAR2 DEFAULT 'Y',
x_return_status IN OUT NOCOPY VARCHAR2
);
PROCEDURE preference_date_nonupdateable(
p_column IN VARCHAR2,
p_column_value IN DATE,
p_old_column_value IN DATE,
p_restricted IN VARCHAR2 DEFAULT 'Y',
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_error BOOLEAN := FALSE;
FND_MESSAGE.SET_NAME( 'AR', 'HZ_NONUPDATEABLE_PREF_DATE' );
END preference_date_nonupdateable;
PROCEDURE get_updated_record (
p_contact_preference_id IN NUMBER,
p_update_field_rec IN HZ_CONTACT_PREFERENCE_V2PUB.CONTACT_PREFERENCE_REC_TYPE,
x_updated_cp_rec OUT NOCOPY HZ_CONTACT_PREFERENCE_V2PUB.CONTACT_PREFERENCE_REC_TYPE
)
IS
BEGIN
SELECT
CONTACT_PREFERENCE_ID,
CONTACT_LEVEL_TABLE,
CONTACT_LEVEL_TABLE_ID,
CONTACT_TYPE,
DECODE ( p_update_field_rec.preference_code, null, PREFERENCE_CODE, p_update_field_rec.preference_code),
DECODE( p_update_field_rec.preference_topic_type, NULL, PREFERENCE_TOPIC_TYPE, FND_API.G_MISS_CHAR, NULL, p_update_field_rec.PREFERENCE_TOPIC_TYPE ),
DECODE( p_update_field_rec.preference_topic_type_id, NULL, PREFERENCE_TOPIC_TYPE_ID, FND_API.G_MISS_NUM, NULL, p_update_field_rec.preference_topic_type_id ),
DECODE( p_update_field_rec.preference_topic_type_code, NULL, PREFERENCE_TOPIC_TYPE_CODE, FND_API.G_MISS_CHAR, NULL, p_update_field_rec.preference_topic_type_code ),
DECODE( p_update_field_rec.preference_start_date, NULL, PREFERENCE_START_DATE, FND_API.G_MISS_DATE, NULL, p_update_field_rec.preference_start_date ),
DECODE( p_update_field_rec.preference_end_date, NULL, PREFERENCE_END_DATE, FND_API.G_MISS_DATE, NULL, p_update_field_rec.preference_end_date ),
DECODE( p_update_field_rec.preference_start_time_hr, NULL, PREFERENCE_START_TIME_HR, FND_API.G_MISS_NUM, NULL, p_update_field_rec.preference_start_time_hr ),
DECODE( p_update_field_rec.preference_end_time_hr, NULL, PREFERENCE_END_TIME_HR, FND_API.G_MISS_NUM, NULL, p_update_field_rec.preference_end_time_hr ),
DECODE( p_update_field_rec.preference_start_time_mi, NULL, PREFERENCE_START_TIME_MI, FND_API.G_MISS_NUM, NULL, p_update_field_rec.preference_start_time_mi ),
DECODE( p_update_field_rec.preference_end_time_mi, NULL, PREFERENCE_END_TIME_MI, FND_API.G_MISS_NUM, NULL, p_update_field_rec.preference_end_time_mi ),
DECODE( p_update_field_rec.max_no_of_interactions, NULL, MAX_NO_OF_INTERACTIONS, FND_API.G_MISS_NUM, NULL, p_update_field_rec.max_no_of_interactions),
DECODE( p_update_field_rec.max_no_of_interact_uom_code, NULL, MAX_NO_OF_INTERACT_UOM_CODE, FND_API.G_MISS_CHAR, NULL, p_update_field_rec.max_no_of_interact_uom_code ),
DECODE( p_update_field_rec.requested_by, NULL, REQUESTED_BY, FND_API.G_MISS_CHAR, NULL, p_update_field_rec.requested_by ),
DECODE( p_update_field_rec.reason_code, NULL, REASON_CODE, FND_API.G_MISS_CHAR, NULL, p_update_field_rec.REASON_CODE ),
DECODE( p_update_field_rec.status, NULL, STATUS, p_update_field_rec.status )
INTO
x_updated_cp_rec.CONTACT_PREFERENCE_ID,
x_updated_cp_rec.CONTACT_LEVEL_TABLE,
x_updated_cp_rec.CONTACT_LEVEL_TABLE_ID,
x_updated_cp_rec.CONTACT_TYPE,
x_updated_cp_rec.PREFERENCE_CODE,
x_updated_cp_rec.PREFERENCE_TOPIC_TYPE,
x_updated_cp_rec.PREFERENCE_TOPIC_TYPE_ID,
x_updated_cp_rec.PREFERENCE_TOPIC_TYPE_CODE,
x_updated_cp_rec.PREFERENCE_START_DATE,
x_updated_cp_rec.PREFERENCE_END_DATE,
x_updated_cp_rec.PREFERENCE_START_TIME_HR,
x_updated_cp_rec.PREFERENCE_END_TIME_HR,
x_updated_cp_rec.PREFERENCE_START_TIME_MI,
x_updated_cp_rec.PREFERENCE_END_TIME_MI,
x_updated_cp_rec.MAX_NO_OF_INTERACTIONS,
x_updated_cp_rec.MAX_NO_OF_INTERACT_UOM_CODE,
x_updated_cp_rec.REQUESTED_BY,
x_updated_cp_rec.REASON_CODE,
x_updated_cp_rec.STATUS
FROM HZ_CONTACT_PREFERENCES
WHERE CONTACT_PREFERENCE_ID = p_contact_preference_id;
END get_updated_record;
p_create_update_flag IN VARCHAR2,
p_contact_preference_rec IN HZ_CONTACT_PREFERENCE_V2PUB.CONTACT_PREFERENCE_REC_TYPE,
p_rowid IN ROWID,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(100) := ''; -- 'validate_contact_preference'
IF p_create_update_flag = 'C' THEN
--If primary key value is passed, check for uniqueness.
IF p_contact_preference_rec.contact_preference_id IS NOT NULL AND
p_contact_preference_rec.contact_preference_id <> FND_API.G_MISS_NUM
THEN
BEGIN
SELECT 'Y' INTO l_dummy
FROM HZ_CONTACT_PREFERENCES
WHERE CONTACT_PREFERENCE_ID = p_contact_preference_rec.contact_preference_id;
IF p_create_update_flag = 'U' THEN
SELECT CONTACT_PREFERENCE_ID, CONTACT_LEVEL_TABLE,
CONTACT_LEVEL_TABLE_ID, CONTACT_TYPE,
PREFERENCE_CODE, PREFERENCE_START_DATE, PREFERENCE_END_DATE,
PREFERENCE_TOPIC_TYPE,PREFERENCE_TOPIC_TYPE_CODE,
PREFERENCE_START_TIME_HR,PREFERENCE_END_TIME_HR,
PREFERENCE_START_TIME_MI,PREFERENCE_END_TIME_MI,
MAX_NO_OF_INTERACT_UOM_CODE, REASON_CODE, REQUESTED_BY, STATUS ,
CREATED_BY_MODULE, APPLICATION_ID
INTO l_contact_preference_id, l_contact_level_table,
l_contact_level_table_id, l_contact_type,
l_preference_code, l_preference_start_date, l_preference_end_date,
l_preference_topic_type, l_preference_topic_type_code,
l_preference_start_time_hr, l_preference_end_time_hr,
l_preference_start_time_mi, l_preference_end_time_mi,
l_max_no_of_interact_uom_code, l_reason_code, l_requested_by, l_status ,
l_created_by_module, l_application_id
FROM HZ_CONTACT_PREFERENCES
WHERE ROWID = p_rowid;
IF p_create_update_flag = 'C' THEN
HZ_UTILITY_V2PUB.validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'contact_level_table',
p_column_value => p_contact_preference_rec.contact_level_table,
x_return_status => x_return_status );
IF p_create_update_flag = 'U' THEN
HZ_UTILITY_V2PUB.validate_nonupdateable (
p_column => 'contact_level_table',
p_column_value => p_contact_preference_rec.contact_level_table,
p_old_column_value => l_contact_level_table,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'contact_level_table is non-updateable.' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'contact_level_table',
p_lookup_type => 'SUBJECT_TABLE',
p_column_value => p_contact_preference_rec.contact_level_table,
x_return_status => x_return_status );
IF p_create_update_flag = 'C' THEN
HZ_UTILITY_V2PUB.validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'contact_level_table_id',
p_column_value => p_contact_preference_rec.contact_level_table_id,
x_return_status => x_return_status );
IF p_create_update_flag = 'U' THEN
HZ_UTILITY_V2PUB.validate_nonupdateable (
p_column => 'contact_level_table_id',
p_column_value => p_contact_preference_rec.contact_level_table_id,
p_old_column_value => l_contact_level_table_id,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'contact_level_table_id is non-updateable.' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' THEN
IF p_contact_preference_rec.contact_level_table = 'HZ_PARTIES' THEN
BEGIN
SELECT 'Y' INTO l_dummy
FROM HZ_PARTIES
WHERE PARTY_ID = p_contact_preference_rec.contact_level_table_id;
SELECT 'Y' INTO l_dummy
FROM HZ_PARTY_SITES
WHERE PARTY_SITE_ID = p_contact_preference_rec.contact_level_table_id;
SELECT 'Y' INTO l_dummy
FROM HZ_CONTACT_POINTS
WHERE CONTACT_POINT_ID = p_contact_preference_rec.contact_level_table_id;
IF p_create_update_flag = 'C' THEN
HZ_UTILITY_V2PUB.validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'contact_type',
p_column_value => p_contact_preference_rec.contact_type,
x_return_status => x_return_status );
IF p_create_update_flag = 'U' THEN
HZ_UTILITY_V2PUB.validate_nonupdateable (
p_column => 'contact_type',
p_column_value => p_contact_preference_rec.contact_type,
p_old_column_value => l_contact_type,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'contact_type is non-updateable.' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'contact_type',
p_lookup_type => 'CONTACT_TYPE',
p_column_value => p_contact_preference_rec.contact_type,
x_return_status => x_return_status );
select contact_point_type,
decode(contact_point_type, 'PHONE', 'CALL', 'FAX', 'FAX',
'SMS', 'SMS', 'EMAIL','EMAIL', 'TLX', 'TLX', 'EDI', 'EDI', contact_point_type)
into l_contact_point_type, l_correct_contact_type
FROM HZ_CONTACT_POINTS
WHERE contact_point_id = p_contact_preference_rec.contact_level_table_id;
p_create_update_flag => p_create_update_flag,
p_column => 'preference_code',
p_column_value => p_contact_preference_rec.preference_code,
x_return_status => x_return_status );
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_contact_preference_rec.preference_code <> NVL (l_preference_code, FND_API.G_MISS_CHAR))) THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'preference_code',
p_lookup_type => 'PREFERENCE_CODE',
p_column_value => p_contact_preference_rec.preference_code,
x_return_status => x_return_status );
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_contact_preference_rec.preference_topic_type <> NVL (l_preference_topic_type, FND_API.G_MISS_CHAR))) THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'preference_topic_type',
p_lookup_type => 'PREFERENCE_TOPIC_TYPE',
p_column_value => p_contact_preference_rec.preference_topic_type,
x_return_status => x_return_status );
SELECT TAG
INTO l_tag
FROM FND_LOOKUP_VALUES
WHERE
LANGUAGE = userenv('LANG') AND
START_DATE_ACTIVE < sysdate AND
( END_DATE_ACTIVE is null OR
END_DATE_ACTIVE = FND_API.G_MISS_DATE OR
END_DATE_ACTIVE > sysdate) AND
LOOKUP_TYPE = 'PREFERENCE_TOPIC_TYPE' AND
LOOKUP_CODE = p_contact_preference_rec.preference_topic_type;
p_create_update_flag => p_create_update_flag,
p_column => 'preference_topic_type_id',
p_column_value => p_contact_preference_rec.preference_topic_type_id,
x_return_status => x_return_status );
SELECT 'Y' INTO l_dummy
FROM AMS_SOURCE_CODES
WHERE source_code_id = p_contact_preference_rec.preference_topic_type_id;
SELECT 'Y' INTO l_dummy
FROM AS_INTEREST_TYPES_B
WHERE interest_type_id = p_contact_preference_rec.preference_topic_type_id;
SELECT 'Y' INTO l_dummy
FROM AS_INTEREST_CODES_B
WHERE interest_code_id = p_contact_preference_rec.preference_topic_type_id;
p_create_update_flag => p_create_update_flag,
p_column => 'preference_topic_type_code',
p_column_value => p_contact_preference_rec.preference_topic_type_code,
x_return_status => x_return_status );
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_contact_preference_rec.preference_topic_type_code <> NVL (l_preference_topic_type_code, FND_API.G_MISS_CHAR))) THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'preference_topic_type_code',
p_lookup_type => p_contact_preference_rec.preference_topic_type,
p_column_value => p_contact_preference_rec.preference_topic_type_code,
x_return_status => x_return_status );
p_create_update_flag => p_create_update_flag,
p_column => 'preference_topic_type_code',
p_column_value => p_contact_preference_rec.preference_topic_type_code,
x_return_status => x_return_status );
p_create_update_flag => p_create_update_flag,
p_column => 'preference_start_date',
p_column_value => p_contact_preference_rec.preference_start_date,
x_return_status => x_return_status );
IF p_create_update_flag = 'C' AND
p_contact_preference_rec.preference_end_date is not null AND
p_contact_preference_rec.preference_end_date <> FND_API.G_MISS_DATE AND
trunc(p_contact_preference_rec.preference_end_date) < trunc(sysdate) THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_INVALID_PREFERENCE_END_DATE' );
IF p_create_update_flag = 'U' AND trunc(p_contact_preference_rec.preference_start_date) < trunc(sysdate) THEN
preference_date_nonupdateable(
p_column => 'preference_start_date',
p_column_value => p_contact_preference_rec.preference_start_date,
p_old_column_value => l_preference_start_date,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'preference_start_date is non-updateable when the new date is less than sysdate.' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'U' AND trunc(p_contact_preference_rec.preference_end_date) < trunc(sysdate) THEN
preference_date_nonupdateable(
p_column => 'preference_end_date',
p_column_value => p_contact_preference_rec.preference_end_date,
p_old_column_value => l_preference_end_date,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'preference_end_date is non-updateable when the new date is less than sysdate.' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
p_create_update_flag => p_create_update_flag,
p_start_date_column_name => 'preference start date',
p_start_date => p_contact_preference_rec.preference_start_date,
p_old_start_date => l_preference_start_date,
p_end_date_column_name => 'Preference end date', -- Bug 4954622
p_end_date => p_contact_preference_rec.preference_end_date,
p_old_end_date => l_preference_end_date,
x_return_status => x_return_status );
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_contact_preference_rec.max_no_of_interact_uom_code <> NVL (l_max_no_of_interact_uom_code, FND_API.G_MISS_CHAR))) THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'max_no_of_interact_uom_code',
p_lookup_type => 'MAX_NO_OF_INTERACT_UOM_CODE',
p_column_value => p_contact_preference_rec.max_no_of_interact_uom_code,
x_return_status => x_return_status );
p_create_update_flag => p_create_update_flag,
p_column => 'preference_start_date',
p_column_value => p_contact_preference_rec.preference_start_date,
x_return_status => x_return_status );
p_create_update_flag => p_create_update_flag,
p_column => 'requested_by',
p_column_value => p_contact_preference_rec.requested_by,
x_return_status => x_return_status );
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_contact_preference_rec.requested_by <> NVL (l_requested_by, FND_API.G_MISS_CHAR))) THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'requested_by',
p_lookup_type => 'REQUESTED_BY',
p_column_value => p_contact_preference_rec.requested_by,
x_return_status => x_return_status );
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_contact_preference_rec.reason_code <> NVL (l_reason_code, FND_API.G_MISS_CHAR))) THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'reason_code',
p_lookup_type => 'REASON_CODE',
p_column_value => p_contact_preference_rec.reason_code,
x_return_status => x_return_status );
IF p_create_update_flag = 'U' THEN
HZ_UTILITY_V2PUB.validate_cannot_update_to_null (
p_column => 'status',
p_column_value => p_contact_preference_rec.status,
x_return_status => x_return_status );
hz_utility_v2pub.debug(p_message=>'status cannot be updated to null.' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
( p_create_update_flag = 'C' OR
( p_create_update_flag = 'U' AND
p_contact_preference_rec.status <> NVL (l_status, FND_API.G_MISS_CHAR))) THEN
HZ_UTILITY_V2PUB.validate_lookup (
p_column => 'status',
p_lookup_type => 'CODE_STATUS',
p_column_value => p_contact_preference_rec.status,
x_return_status => x_return_status );
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_contact_preference_rec.created_by_module,
p_old_created_by_module => l_created_by_module,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_application_id => p_contact_preference_rec.application_id,
p_old_application_id => l_application_id,
x_return_status => x_return_status);
IF p_create_update_flag = 'C' THEN
BEGIN
SELECT contact_preference_id INTO l_contact_pref_dup_id
FROM HZ_CONTACT_PREFERENCES
WHERE
-- check contact_level_table and contact_level_table_id
contact_level_table = p_contact_preference_rec.contact_level_table AND
contact_level_table_id = p_contact_preference_rec.contact_level_table_id AND
status = 'A' AND
/*Bug Number 3067948.According to bug number 1919493,end_date and start_date can be trunc(sysdate) )) AND
*/
-- check other unique column
contact_type || preference_topic_type ||
preference_topic_type_id || preference_topic_type_code
=
p_contact_preference_rec.contact_type ||
p_contact_preference_rec.preference_topic_type ||
p_contact_preference_rec.preference_topic_type_id ||
p_contact_preference_rec.preference_topic_type_code AND
-- check preference_start_date preference_end_date overlap
NOT ( ( p_contact_preference_rec.preference_end_date is not null AND
--Bug Number 3067948.
p_contact_preference_rec.preference_end_date <> fnd_api.g_miss_date AND
p_contact_preference_rec.preference_end_date < preference_start_date ) OR
( preference_end_date is not null and
p_contact_preference_rec.preference_start_date > preference_end_date )) AND
-- check preference_start_time_hr/mi preference_end_time_hr/mi overlap
NOT ( ( decode(preference_start_time_hr, null, 0, preference_start_time_hr) * 60 +
decode(preference_start_time_mi, null, 0, preference_start_time_mi) >
decode(p_contact_preference_rec.preference_end_time_hr, null, 24, p_contact_preference_rec.preference_end_time_hr) * 60 +
decode (p_contact_preference_rec.preference_end_time_mi, null, 60, p_contact_preference_rec.preference_end_time_mi) ) OR
( decode(preference_end_time_hr, null, 24, preference_end_time_hr ) * 60 +
decode(preference_end_time_mi, null, 60, preference_end_time_mi ) <
decode(p_contact_preference_rec.preference_start_time_hr, null, 0, p_contact_preference_rec.preference_start_time_hr) * 60 +
decode(p_contact_preference_rec.preference_start_time_mi, null, 0, p_contact_preference_rec.preference_start_time_mi ) ) ) AND
ROWNUM =1;
ELSIF p_create_update_flag = 'U' THEN
BEGIN
get_updated_record (
p_contact_preference_id => l_contact_preference_id,
p_update_field_rec => p_contact_preference_rec,
x_updated_cp_rec => l_contact_preference_rec
);
SELECT contact_preference_id INTO l_contact_pref_dup_id
FROM HZ_CONTACT_PREFERENCES
WHERE
-- check contact_level_table and contact_level_table_id
contact_preference_id <> l_contact_preference_id AND
contact_level_table = l_contact_preference_rec.contact_level_table AND
contact_level_table_id = l_contact_preference_rec.contact_level_table_id AND
status = 'A' AND
/*Bug Number 3067948.According to bug number 1919493,end_date and start_date can be trunc(sysdate) )) AND
*/
-- check other unique column
contact_type || preference_topic_type ||
preference_topic_type_id || preference_topic_type_code
=
l_contact_preference_rec.contact_type ||
l_contact_preference_rec.preference_topic_type ||
l_contact_preference_rec.preference_topic_type_id ||
l_contact_preference_rec.preference_topic_type_code AND
-- check preference_start_date preference_end_date overlap
NOT ( ( l_contact_preference_rec.preference_end_date is not null and
l_contact_preference_rec.preference_end_date < preference_start_date ) OR
( preference_end_date is not null and
l_contact_preference_rec.preference_start_date > preference_end_date )) AND
-- check preference_start_time_hr/mi preference_end_time_hr/mi overlap
NOT ( ( decode(preference_start_time_hr, null, 0, preference_start_time_hr) * 60 +
decode(preference_start_time_mi, null, 0, preference_start_time_mi) >
decode(l_contact_preference_rec.preference_end_time_hr, null, 24, l_contact_preference_rec.preference_end_time_hr) * 60 +
decode (l_contact_preference_rec.preference_end_time_mi, null, 60, l_contact_preference_rec.preference_end_time_mi) ) OR
( decode(preference_end_time_hr, null, 24, preference_end_time_hr ) * 60 +
decode(preference_end_time_mi, null, 60, preference_end_time_mi ) <
decode(l_contact_preference_rec.preference_start_time_hr, null, 0, l_contact_preference_rec.preference_start_time_hr) * 60 +
decode(l_contact_preference_rec.preference_start_time_mi, null, 0, l_contact_preference_rec.preference_start_time_mi ) ) ) AND
ROWNUM =1;