[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_create_update_flag IN VARCHAR2,
p_created_by_module IN VARCHAR2,
p_old_created_by_module IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
);
p_create_update_flag IN VARCHAR2,
p_application_id IN NUMBER,
p_old_application_id IN NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
);
SELECT 'Y'
FROM fnd_lookup_values
WHERE lookup_type = p_lookup_type
AND replace(lookup_code, '.', '') = replace(p_column_value, '.', '')
AND ROWNUM = 1;
SELECT 'Y' INTO l_dummy
FROM AR_LOOKUPS
WHERE LOOKUP_TYPE = l_lookup_type
AND LOOKUP_CODE = l_lookup_code
AND (ENABLED_FLAG = 'Y' AND
TRUNC(SYSDATE) BETWEEN
TRUNC(NVL(START_DATE_ACTIVE,SYSDATE)) AND
TRUNC(NVL(END_DATE_ACTIVE,SYSDATE))
);
SELECT 'Y' INTO l_dummy
FROM SO_LOOKUPS
WHERE LOOKUP_TYPE = l_lookup_type
AND LOOKUP_CODE = l_lookup_code
AND (ENABLED_FLAG = 'Y' AND
TRUNC(SYSDATE) BETWEEN
TRUNC(NVL(START_DATE_ACTIVE,SYSDATE)) AND
TRUNC(NVL(END_DATE_ACTIVE,SYSDATE))
);
SELECT 'Y' INTO l_dummy
FROM OE_SHIP_METHODS_V
WHERE LOOKUP_TYPE = l_lookup_type
AND LOOKUP_CODE = l_lookup_code
AND (ENABLED_FLAG = 'Y' AND
TRUNC(SYSDATE) BETWEEN
TRUNC(NVL(START_DATE_ACTIVE,SYSDATE)) AND
TRUNC(NVL(END_DATE_ACTIVE,SYSDATE))
)
AND ROWNUM = 1;
SELECT 'Y' INTO l_dummy
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = l_lookup_type
AND LOOKUP_CODE = l_lookup_code
AND (ENABLED_FLAG = 'Y' AND
TRUNC(SYSDATE) BETWEEN
TRUNC(NVL(START_DATE_ACTIVE,SYSDATE)) AND
TRUNC(NVL(END_DATE_ACTIVE,SYSDATE))
)
AND ROWNUM = 1;
SELECT null INTO l_dummy
FROM FND_TERRITORIES
WHERE TERRITORY_CODE = l_territory_code
AND OBSOLETE_FLAG = 'N';
SELECT hp.party_type
FROM hz_parties hp
WHERE hp.party_id = p_party_id;
SELECT hca.class_category,
hca.object_version_number
FROM hz_code_assignments hca
WHERE hca.owner_table_name = 'HZ_PARTIES'
AND hca.owner_table_id = p_party_id
AND hca.status = 'A'
ORDER BY 2 DESC;
p_create_update_flag IN VARCHAR2,
p_column IN VARCHAR2,
p_column_value IN VARCHAR2,
p_restricted IN VARCHAR2 DEFAULT 'N',
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_error BOOLEAN := FALSE;
IF (p_create_update_flag = 'C' AND
(p_column_value IS NULL OR
p_column_value = fnd_api.g_miss_char)) OR
(p_create_update_flag = 'U' AND
p_column_value = fnd_api.g_miss_char)
THEN
l_error := TRUE;
p_create_update_flag IN VARCHAR2,
p_column IN VARCHAR2,
p_column_value IN NUMBER,
p_restricted IN VARCHAR2 DEFAULT 'N',
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_error BOOLEAN := FALSE;
IF (p_create_update_flag = 'C' AND
(p_column_value IS NULL OR
p_column_value = fnd_api.g_miss_num)) OR
(p_create_update_flag = 'U' AND
p_column_value = fnd_api.g_miss_num)
THEN
l_error := TRUE;
p_create_update_flag IN VARCHAR2,
p_column IN VARCHAR2,
p_column_value IN DATE,
p_restricted IN VARCHAR2 DEFAULT 'N',
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_error BOOLEAN := FALSE;
IF (p_create_update_flag = 'C' AND
(p_column_value IS NULL OR
p_column_value = FND_API.G_MISS_DATE)) OR
(p_create_update_flag = 'U' AND
p_column_value = FND_API.G_MISS_DATE)
THEN
l_error := TRUE;
PROCEDURE validate_nonupdateable (
p_column IN VARCHAR2,
p_column_value IN VARCHAR2,
p_old_column_value IN VARCHAR2,
p_restricted IN VARCHAR2 DEFAULT 'Y',
x_return_status IN OUT NOCOPY VARCHAR2,
p_raise_error IN VARCHAR2 := 'Y'
) IS
l_error BOOLEAN := FALSE;
fnd_message.set_name('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
END validate_nonupdateable;
PROCEDURE validate_nonupdateable (
p_column IN VARCHAR2,
p_column_value IN NUMBER,
p_old_column_value IN NUMBER,
p_restricted IN VARCHAR2 DEFAULT 'Y',
x_return_status IN OUT NOCOPY VARCHAR2,
p_raise_error IN VARCHAR2 := 'Y'
) IS
l_error BOOLEAN := FALSE;
fnd_message.set_name('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
END validate_nonupdateable;
PROCEDURE validate_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,
p_raise_error IN VARCHAR2 := 'Y'
) IS
l_error BOOLEAN := FALSE;
fnd_message.set_name('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
END validate_nonupdateable;
p_create_update_flag IN VARCHAR2,
p_start_date_column_name IN VARCHAR2,
p_start_date IN DATE,
p_old_start_date IN DATE,
p_end_date_column_name IN VARCHAR2,
p_end_date IN DATE,
p_old_end_date IN DATE,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_start_date DATE := p_old_start_date;
IF p_create_update_flag = 'C' THEN
l_start_date := p_start_date;
ELSIF p_create_update_flag = 'U' THEN
IF p_start_date IS NOT NULL
THEN
IF p_start_date = FND_API.G_MISS_DATE THEN
l_start_date := NULL;
PROCEDURE validate_cannot_update_to_null (
p_column IN VARCHAR2,
p_column_value IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
BEGIN
IF p_column_value = fnd_api.g_miss_char THEN
fnd_message.set_name('AR', 'HZ_API_NONUPDATEABLE_TO_NULL');
END validate_cannot_update_to_null;
PROCEDURE validate_cannot_update_to_null (
p_column IN VARCHAR2,
p_column_value IN NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
BEGIN
IF p_column_value = fnd_api.g_miss_num THEN
fnd_message.set_name('AR', 'HZ_API_NONUPDATEABLE_TO_NULL');
END validate_cannot_update_to_null;
PROCEDURE validate_cannot_update_to_null (
p_column IN VARCHAR2,
p_column_value IN DATE,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
BEGIN
IF p_column_value = FND_API.G_MISS_DATE THEN
fnd_message.set_name('AR', 'HZ_API_NONUPDATEABLE_TO_NULL');
END validate_cannot_update_to_null;
p_create_update_flag IN VARCHAR2,
p_contact_point_rec IN hz_contact_point_v2pub.contact_point_rec_type,
p_edi_rec IN hz_contact_point_v2pub.edi_rec_type := hz_contact_point_v2pub.g_miss_edi_rec,
p_eft_rec IN hz_contact_point_v2pub.eft_rec_type := hz_contact_point_v2pub.g_miss_eft_rec,
p_email_rec IN hz_contact_point_v2pub.email_rec_type := hz_contact_point_v2pub.g_miss_email_rec,
p_phone_rec IN hz_contact_point_v2pub.phone_rec_type := hz_contact_point_v2pub.g_miss_phone_rec,
p_telex_rec IN hz_contact_point_v2pub.telex_rec_type := hz_contact_point_v2pub.g_miss_telex_rec,
p_web_rec IN hz_contact_point_v2pub.web_rec_type := hz_contact_point_v2pub.g_miss_web_rec,
p_rowid IN ROWID,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := ''; --'validate_contact_point'
CURSOR c_update IS
SELECT hcp.owner_table_name,
hcp.owner_table_id,
hcp.contact_point_type,
hcp.content_source_type,
hcp.orig_system_reference,
hcp.status,
hcp.primary_flag,
hcp.created_by_module,
hcp.application_id,
hcp.contact_point_purpose,
hcp.email_format,
hcp.phone_line_type,
hcp.phone_country_code,
hcp.primary_by_purpose,
hcp.actual_content_source,
hcp.phone_area_code,
hcp.phone_country_code,
hcp.phone_number,
hcp.phone_extension,
hcp.raw_phone_number,
-- Bug 4226199 : Added for update validation
hcp.email_address,
hcp.telex_number,
hcp.url
FROM hz_contact_points hcp
WHERE ROWID = p_rowid;
SELECT 'Y'
FROM hz_contact_points hcp
WHERE hcp.contact_point_id = p_contact_point_id;
SELECT 'Y'
FROM hz_parties
WHERE party_id = p_owner_table_id;
SELECT 'Y'
FROM hz_party_sites hps
WHERE hps.party_site_id = p_owner_table_id;
CURSOR c_timezone (p_timezone_id IN NUMBER) IS--updated against bug 7046491
SELECT 'Y'
FROM fnd_timezones_vl ftl
WHERE ftl.upgrade_tz_id = p_timezone_id;
SELECT 'Y'
FROM hz_phone_country_codes hpcc
WHERE hpcc.phone_country_code = p_phone_country_code
AND ROWNUM = 1;
IF p_create_update_flag = 'U' THEN
-- Bug 2197181: selecting actual_content_source for mix-n-match
-- Bug 4203495: selecting primary_flag
OPEN c_update;
FETCH c_update
INTO l_owner_table_name,
l_owner_table_id,
l_contact_point_type,
l_content_source_type,
l_orig_system_reference,
l_status,
l_primary_flag,
l_created_by_module,
l_application_id,
l_contact_point_purpose,
l_email_format,
l_phone_line_type,
l_phone_country_code,
l_primary_by_purpose,
db_actual_content_source,
l_phone_area_code,
l_phone_country_code,
l_phone_number,
l_phone_extension,
l_raw_phone_number,
-- Bug 4226199 : Added for update validation
l_email_address,
l_telex,
l_url;
IF c_update%NOTFOUND THEN
CLOSE c_update;
hz_utility_v2pub.debug ('could not find record to update, rowid='||
p_rowid);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'could not find record to update, rowid='||p_rowid,
p_msg_level=>fnd_log.level_statement);
CLOSE c_update;
IF p_create_update_flag = 'C' THEN
IF p_contact_point_rec.contact_point_id IS NOT NULL AND
p_contact_point_rec.contact_point_id <> fnd_api.g_miss_num
THEN
OPEN c_dup (p_contact_point_rec.contact_point_id);
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'contact_point_type',
p_column_value => p_contact_point_rec.contact_point_type,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' AND
p_contact_point_rec.contact_point_type IS NOT NULL
THEN
validate_nonupdateable (
p_column => 'contact_point_type',
p_column_value => p_contact_point_rec.contact_point_type,
p_old_column_value => l_contact_point_type,
x_return_status => x_return_status);
'contact_point_type is non-updateable. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'contact_point_type is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' THEN
validate_lookup (
p_column => 'contact_point_type',
p_lookup_type => 'COMMUNICATION_TYPE',
p_column_value => p_contact_point_rec.contact_point_type,
x_return_status => x_return_status);
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'owner_table_name',
p_column_value => p_contact_point_rec.owner_table_name,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' AND
p_contact_point_rec.owner_table_name IS NOT NULL
THEN
validate_nonupdateable (
p_column => 'owner_table_name',
p_column_value => p_contact_point_rec.owner_table_name,
p_old_column_value => l_owner_table_name,
x_return_status => x_return_status);
'owner_table_name is non-updateable. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'owner_table_name is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' THEN
validate_lookup (
p_column => 'owner_table_name',
p_lookup_type => 'OWNER_TABLE_NAME',
p_column_value => p_contact_point_rec.owner_table_name,
x_return_status => x_return_status);
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'owner_table_id',
p_column_value => p_contact_point_rec.owner_table_id,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' AND
p_contact_point_rec.owner_table_id IS NOT NULL
THEN
validate_nonupdateable (
p_column => 'owner_table_id',
p_column_value => p_contact_point_rec.owner_table_id,
p_old_column_value => l_owner_table_id,
x_return_status => x_return_status);
'owner_table_id is non-updateable. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'owner_table_id is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' THEN
IF p_contact_point_rec.owner_table_name = 'HZ_PARTIES' THEN
OPEN c_pexist(p_contact_point_rec.owner_table_id);
and p_create_update_flag = 'U'
then
hz_orig_system_ref_pub.get_owner_table_id
(p_orig_system => p_contact_point_rec.orig_system,
p_orig_system_reference => p_contact_point_rec.orig_system_reference,
p_owner_table_name => 'HZ_CONTACT_POINTS',
x_owner_table_id => l_mosr_owner_table_id,
x_return_status => l_temp_return_status);
IF p_create_update_flag = 'U' AND
p_contact_point_rec.orig_system_reference IS NOT NULL and l_validate_osr = 'Y'
THEN
validate_nonupdateable (
p_column => 'orig_system_reference',
p_column_value => p_contact_point_rec.orig_system_reference,
p_old_column_value => l_orig_system_reference,
x_return_status => x_return_status);
'orig_system_reference is non-updateable. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'orig_system_reference is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'content_source_type',
p_column_value => p_contact_point_rec.content_source_type,
p_old_column_value => l_content_source_type,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'content_source_type is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' AND
p_contact_point_rec.content_source_type <>
hz_party_v2pub.g_miss_content_source_type
THEN
validate_lookup (
p_column => 'content_source_type',
p_lookup_type => 'CONTENT_SOURCE_TYPE',
p_column_value => p_contact_point_rec.content_source_type,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_check_update_privilege => 'N',
p_content_source_type => p_contact_point_rec.content_source_type,
p_old_content_source_type => l_content_source_type,
p_actual_content_source => p_contact_point_rec.actual_content_source,
p_old_actual_content_source => db_actual_content_source,
p_entity_name => 'HZ_CONTACT_POINTS',
x_return_status => x_return_status );
IF p_create_update_flag = 'U' AND
p_contact_point_rec.status IS NOT NULL
THEN
validate_cannot_update_to_null (
p_column => 'status',
p_column_value => p_contact_point_rec.status,
x_return_status => x_return_status);
'Status cannot be updated to null. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'Status cannot be updated to null. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
(p_create_update_flag = 'C' OR
(p_create_update_flag = 'U' AND
p_contact_point_rec.status <> NVL(l_status, fnd_api.g_miss_char)))
THEN
validate_lookup (
p_column => 'status',
p_lookup_type => 'REGISTRY_STATUS',
p_column_value => p_contact_point_rec.status,
x_return_status => x_return_status);
IF p_create_update_flag = 'C' THEN
IF p_contact_point_rec.primary_flag IS NULL OR
p_contact_point_rec.primary_flag = fnd_api.g_miss_char
THEN
l_primary_flag := 'N';
IF p_create_update_flag = 'C' THEN
IF p_contact_point_rec.primary_by_purpose IS NULL OR
p_contact_point_rec.primary_by_purpose = fnd_api.g_miss_char
THEN
l_preferred_flag := 'N';
IF (p_create_update_flag = 'C')
THEN
IF p_contact_point_rec.primary_by_purpose = 'Y'
THEN
IF (p_contact_point_rec.contact_point_purpose IS NULL OR
p_contact_point_rec.contact_point_purpose = FND_API.G_MISS_CHAR)
THEN
-- Error
FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MAND_DEP_FIELDS' );
ELSIF (p_create_update_flag = 'U') THEN
IF ( p_contact_point_rec.primary_by_purpose = 'Y' OR
( p_contact_point_rec.primary_by_purpose IS NULL AND
l_primary_by_purpose = 'Y'))
THEN
IF ( p_contact_point_rec.contact_point_purpose = FND_API.G_MISS_CHAR OR
( p_contact_point_rec.contact_point_purpose IS NULL AND
l_contact_point_purpose IS NULL))
THEN
-- Error
FND_MESSAGE.SET_NAME( 'AR', 'HZ_API_MAND_DEP_FIELDS' );
p_create_update_flag = 'C') OR
(l_contact_point_type <> 'WEB' AND
p_create_update_flag = 'U')) AND
p_contact_point_rec.contact_point_purpose IS NOT NULL AND
p_contact_point_rec.contact_point_purpose <> fnd_api.g_miss_char AND
(p_create_update_flag = 'C' OR
(p_create_update_flag = 'U' AND
p_contact_point_rec.contact_point_purpose <>
NVL(l_contact_point_purpose, fnd_api.g_miss_char)))
THEN
validate_lookup (
p_column => 'contact_point_purpose',
p_lookup_type => 'CONTACT_POINT_PURPOSE',
p_column_value => p_contact_point_rec.contact_point_purpose,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_contact_point_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_point_rec.application_id,
p_old_application_id => l_application_id,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_column => 'edi_id_number',
p_column_value => p_edi_rec.edi_id_number,
x_return_status => x_return_status);
IF p_create_update_flag = 'C' THEN
validate_party_type(
p_table_name => p_contact_point_rec.owner_table_name,
p_party_id => p_contact_point_rec.owner_table_id,
p_contact_point_type => l_contact_point_type,
x_return_status => x_return_status
);
IF p_create_update_flag = 'C' THEN
validate_party_type(
p_table_name => p_contact_point_rec.owner_table_name,
p_party_id => p_contact_point_rec.owner_table_id,
p_contact_point_type => l_contact_point_type,
x_return_status => x_return_status
);
IF p_create_update_flag = 'U' AND
p_email_rec.email_format IS NOT NULL
THEN
validate_cannot_update_to_null (
p_column => 'email_format',
p_column_value => p_email_rec.email_format,
x_return_status => x_return_status);
'EMAIL : email_format cannot be updated to null. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
p_message=>'EMAIL : email_format cannot be updated to null. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'U' AND
db_actual_content_source <> 'USER_ENTERED' THEN
l_return_status := FND_API.G_RET_STS_SUCCESS;
validate_nonupdateable (
p_column => 'email_address',
p_column_value => p_email_rec.email_address,
p_old_column_value => l_email_address,
x_return_status => l_return_status,
p_raise_error => 'N');
HZ_MIXNM_UTILITY.CheckUserUpdatePrivilege(
p_actual_content_source => db_actual_content_source,
p_new_actual_content_source=> p_contact_point_rec.actual_content_source,
p_entity_name => 'HZ_CONTACT_POINTS',
x_return_status => x_return_status);
HZ_UTILITY_V2PUB.G_UPDATE_ACS := 'Y';
(p_create_update_flag = 'C' OR
(p_create_update_flag = 'U' AND
p_email_rec.email_format <>
NVL(l_email_format, fnd_api.g_miss_char)))
THEN
validate_lookup (
p_column => 'email_format',
p_lookup_type => 'EMAIL_FORMAT',
p_column_value => p_email_rec.email_format,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_column => 'email_address',
p_column_value => p_email_rec.email_address,
x_return_status => x_return_status);
IF p_create_update_flag = 'C' THEN
IF ((p_phone_rec.phone_number IS NULL OR
p_phone_rec.phone_number = fnd_api.g_miss_char) AND
(p_phone_rec.raw_phone_number IS NULL OR
p_phone_rec.raw_phone_number = fnd_api.g_miss_char)) OR
((p_phone_rec.phone_number IS NOT NULL AND
p_phone_rec.phone_number <> fnd_api.g_miss_char) AND
(p_phone_rec.raw_phone_number IS NOT NULL AND
p_phone_rec.raw_phone_number <> fnd_api.g_miss_char))
THEN
l_error := TRUE;
p_create_update_flag => p_create_update_flag,
p_column => 'phone_line_type',
p_column_value => p_phone_rec.phone_line_type,
x_return_status => x_return_status);
(p_create_update_flag = 'C' OR
(p_create_update_flag = 'U' AND
p_phone_rec.phone_line_type <> NVL(l_phone_line_type,
fnd_api.g_miss_char)))
THEN
validate_lookup (
p_column => 'phone_line_type',
p_lookup_type => 'PHONE_LINE_TYPE',
p_column_value => p_phone_rec.phone_line_type,
x_return_status => x_return_status);
IF c_timezone%NOTFOUND THEN--updated against bug 7046491
fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
(p_create_update_flag = 'C' OR
(p_create_update_flag = 'U' AND
p_phone_rec.phone_country_code <> NVL(l_phone_country_code,
fnd_api.g_miss_char)))
THEN
OPEN c_countrycode(p_phone_rec.phone_country_code);
IF p_create_update_flag = 'U'
AND p_phone_rec.raw_phone_number is NULL
THEN
validate_cannot_update_to_null(
p_column => 'phone_number',
p_column_value => p_phone_rec.phone_number,
x_return_status => l_return_status);
'(+) phone_number cannot be updated to NULL... ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
p_message=>'(+) phone_number cannot be updated to NULL... ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'U' AND
l_contact_point_type = 'PHONE' AND
-- Bug 4226199 : Call for all ACS other than UE
db_actual_content_source <> 'USER_ENTERED' AND
(p_phone_rec.phone_area_code||
p_phone_rec.phone_country_code||
p_phone_rec.phone_number||
p_phone_rec.phone_extension||
p_phone_rec.raw_phone_number IS NOT NULL) /* AND
db_actual_content_source = 'DNB' AND*/
-- SSM SST Integration and Extension
--NVL(FND_PROFILE.value('HZ_UPDATE_THIRD_PARTY_DATA'), 'N') = 'N'
-- HZ_UTILITY_V2PUB.is_purchased_content_source(db_actual_content_source) = 'Y'
THEN
l_return_status := FND_API.G_RET_STS_SUCCESS;
validate_nonupdateable (
p_column => 'phone_area_code',
p_column_value => p_phone_rec.phone_area_code,
p_old_column_value => l_phone_area_code,
x_return_status => l_return_status,
p_raise_error => 'N');
validate_nonupdateable (
p_column => 'phone_country_code',
p_column_value => p_phone_rec.phone_country_code,
p_old_column_value => l_phone_country_code,
x_return_status => l_return_status,
p_raise_error => 'N');
validate_nonupdateable (
p_column => 'phone_number',
p_column_value => p_phone_rec.phone_number,
p_old_column_value => l_phone_number,
x_return_status => l_return_status,
p_raise_error => 'N');
validate_nonupdateable (
p_column => 'phone_extension',
p_column_value => p_phone_rec.phone_extension,
p_old_column_value => l_phone_extension,
x_return_status => l_return_status,
p_raise_error => 'N');
validate_nonupdateable (
p_column => 'raw_phone_number',
p_column_value => p_phone_rec.raw_phone_number,
p_old_column_value => l_raw_phone_number,
x_return_status => l_return_status,
p_raise_error => 'N');
HZ_MIXNM_UTILITY.CheckUserUpdatePrivilege(
p_actual_content_source => db_actual_content_source,
p_new_actual_content_source=> p_contact_point_rec.actual_content_source,
p_entity_name => 'HZ_CONTACT_POINTS',
x_return_status => x_return_status);
HZ_UTILITY_V2PUB.G_UPDATE_ACS := 'Y';
/*FND_MESSAGE.SET_NAME('AR', 'HZ_NOTALLOW_UPDATE_THIRD_PARTY');
'dnb phones are non-updateable. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
p_message=>'dnb phones are non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
p_create_update_flag => p_create_update_flag,
p_column => 'telex_number',
p_column_value => p_telex_rec.telex_number,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' AND
db_actual_content_source <> 'USER_ENTERED' THEN
l_return_status := FND_API.G_RET_STS_SUCCESS;
validate_nonupdateable (
p_column => 'telex_number',
p_column_value => p_telex_rec.telex_number,
p_old_column_value => l_telex,
x_return_status => l_return_status,
p_raise_error => 'N');
HZ_MIXNM_UTILITY.CheckUserUpdatePrivilege(
p_actual_content_source => db_actual_content_source,
p_new_actual_content_source=> p_contact_point_rec.actual_content_source,
p_entity_name => 'HZ_CONTACT_POINTS',
x_return_status => x_return_status);
HZ_UTILITY_V2PUB.G_UPDATE_ACS := 'Y';
p_create_update_flag => p_create_update_flag,
p_column => 'web_type',
p_column_value => p_web_rec.web_type,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_column => 'url',
p_column_value => p_web_rec.url,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' AND
db_actual_content_source <> 'USER_ENTERED' THEN
l_return_status := FND_API.G_RET_STS_SUCCESS;
validate_nonupdateable (
p_column => 'url',
p_column_value => p_web_rec.url,
p_old_column_value => l_url,
x_return_status => l_return_status,
p_raise_error => 'N');
HZ_MIXNM_UTILITY.CheckUserUpdatePrivilege(
p_actual_content_source => db_actual_content_source,
p_new_actual_content_source=> p_contact_point_rec.actual_content_source,
p_entity_name => 'HZ_CONTACT_POINTS',
x_return_status => x_return_status);
HZ_UTILITY_V2PUB.G_UPDATE_ACS := 'Y';
(p_create_update_flag = 'C' OR
(p_create_update_flag = 'U' AND
p_contact_point_rec.contact_point_purpose <>
NVL(l_contact_point_purpose, fnd_api.g_miss_char)))
THEN
validate_lookup (
p_column => 'contact_point_purpose',
p_lookup_type => 'CONTACT_POINT_PURPOSE_WEB',
p_column_value => p_contact_point_rec.contact_point_purpose,
x_return_status => x_return_status);
* non-updateable fields
* foreign key validations
* other validations
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_person_rec Person record.
* p_old_person_rec Old person record.
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* FND_API.G_RET_STS_ERROR (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Indrajit Sen o Created.
* 09-26-2003 Rajib Ranjan Borah o Commented out the validate HR security code
* and added validation code in
* validate_hr_security procedure
* which is called from validate_person
* (Bug 3099624)
*/
PROCEDURE validate_party(
p_create_update_flag IN VARCHAR2,
p_party_rec IN HZ_PARTY_V2PUB.PARTY_REC_TYPE,
p_old_party_rec IN HZ_PARTY_V2PUB.PARTY_REC_TYPE,
p_db_created_by_module IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := '';
select 1
from per_all_people_f
where party_id = p_party_rec.party_id;
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'party_number',
p_column_value => p_party_rec.party_number,
p_old_column_value => p_old_party_rec.party_number,
x_return_status => x_return_status);
'party_number is non-updateable. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'party_number is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
and p_create_update_flag = 'U'
then
hz_orig_system_ref_pub.get_owner_table_id
(p_orig_system => p_party_rec.orig_system,
p_orig_system_reference => p_party_rec.orig_system_reference,
p_owner_table_name => 'HZ_PARTIES',
x_owner_table_id => l_mosr_owner_table_id,
x_return_status => l_temp_return_status);
IF p_create_update_flag = 'U' and l_validate_osr = 'Y' THEN
validate_nonupdateable (
p_column => 'orig_system_reference',
p_column_value => p_party_rec.orig_system_reference,
p_old_column_value => p_old_party_rec.orig_system_reference,
x_return_status => x_return_status);
'orig_system_reference is non-updateable. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'orig_system_reference is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'U' THEN
validate_cannot_update_to_null (
p_column => 'status',
p_column_value => p_party_rec.status,
x_return_status => x_return_status);
'status is not updateable to null. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'status is not updateable to null. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_party_rec.status <> p_old_party_rec.status
)
)
THEN
validate_lookup (
p_column => 'status',
p_lookup_type => 'REGISTRY_STATUS',
p_column_value => p_party_rec.status,
x_return_status => x_return_status);
| AND p_create_update_flag = 'U'
| THEN
| fnd_message.set_name('AR', 'HZ_CREATED_BY_MISMATCH');
| AND p_create_update_flag = 'U'
|THEN
| fnd_message.set_name('AR', 'HZ_CREATED_BY_MISMATCH');
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_party_rec.category_code <> p_old_party_rec.category_code
)
)
THEN
validate_lookup (
p_column => 'category_code',
p_lookup_type => 'CUSTOMER_CATEGORY',
p_column_value => p_party_rec.category_code,
x_return_status => x_return_status);
* non-updateable fields
* foreign key validations
* hr security validation
* other validations
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_person_rec Person record.
* p_old_person_rec Old person record.
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* fnd_api.g_ret_sts_error (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Indrajit Sen o Created.
* 06-FEB-2003 Porkodi C o Bug 2684319: Added a validation for deceased_flag field.
* 10-Mar-2003 Porkodi Chinnandar o Bug 2817974, Added a validation for date_of_death and
* date_of_birth field to have greater value for date_of_death.
* 27-Mar-2003 Porkodi C o Bug 2794173: Added g_miss_date check for date_of_death
* 26-Sep-2003 Rajib Ranjan Borah o Bug 3099624: Called the validate_hr_security
* procedure to validate HR security.
* 16-JAN-2004 Rajib Ranjan Borah o Bug 3333036.Rent_own_ind is now validated only if it
* has been updated and not for each record.
* 13-JUL-2004 V.Ravichandran o Bug 3704293 : Modified the code which validates
* date_of_birth and date_of_death combination in
* validate_person() procedure.
* 25-AUG-2004 V.Ravichandran o Bug 3747386 : Modified the code which validates
* that both first_name and last_name
* should not be null during update in validate_person()
* procedure.
* 31-MAY-2006 Nishant Singhai o Bug 5174379 : Person Name update allowed check added.
*/
PROCEDURE validate_person(
p_create_update_flag IN VARCHAR2,
p_person_rec IN HZ_PARTY_V2PUB.PERSON_REC_TYPE,
p_old_person_rec IN HZ_PARTY_V2PUB.PERSON_REC_TYPE,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := '';
-- person_name_suffix are allowed to be updated or not
-- Update only if profile is set to Y or it is not set at all. If it is set
-- to 'N' update is not allowed.
-- Check added for Bug 5174379 on 31-May-2006 (Nishant)
-------------------------------------------------------------------------
IF p_create_update_flag = 'U' THEN
l_change_cust_name_profile := fnd_profile.VALUE('AR_CHANGE_CUST_NAME');
IF (NVL(l_change_cust_name_profile,'Y') = 'N') THEN -- update to party name is not allowed
IF ((p_person_rec.person_first_name <> p_old_person_rec.person_first_name) OR
(p_person_rec.person_middle_name <> p_old_person_rec.person_middle_name) OR
(p_person_rec.person_last_name <> p_old_person_rec.person_last_name) OR
(p_person_rec.person_pre_name_adjunct <> p_old_person_rec.person_pre_name_adjunct) OR
(p_person_rec.person_name_suffix <> p_old_person_rec.person_name_suffix)
) THEN
fnd_message.set_name('AR', 'HZ_CUST_NAME_UPDT_NOT_ALLOWED');
p_message=>'Person Name Update Allowed Check...' ||
'x_return_status : ' || x_return_status ||
'. Profile AR_CHANGE_CUST_NAME value :'||l_change_cust_name_profile,
p_msg_level=>fnd_log.level_statement);
END IF; -- create update flag = U
IF p_create_update_flag = 'C' THEN
IF (p_person_rec.person_first_name = fnd_api.g_miss_char or p_person_rec.person_first_name IS NULL)
AND
(p_person_rec.person_last_name = fnd_api.g_miss_char or p_person_rec.person_last_name IS NULL)
THEN
fnd_message.set_name('AR', 'HZ_FIRST_OR_LAST_NAME_REQUIRED');
IF p_create_update_flag = 'U' THEN
-- Bug 3747386
IF (p_person_rec.person_first_name = fnd_api.g_miss_char
or NVL(p_person_rec.person_first_name,p_old_person_rec.person_first_name)=fnd_api.g_miss_char)
AND
(p_person_rec.person_last_name = fnd_api.g_miss_char
or NVL(p_person_rec.person_last_name,p_old_person_rec.person_last_name)=fnd_api.g_miss_char) THEN
fnd_message.set_name('AR', 'HZ_FIRST_OR_LAST_NAME_REQUIRED');
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_person_rec.person_pre_name_adjunct <> p_old_person_rec.person_pre_name_adjunct
)
)
THEN
validate_lookup (
p_column => 'person_pre_name_adjunct',
p_lookup_type => 'CONTACT_TITLE',
p_column_value => p_person_rec.person_pre_name_adjunct,
x_return_status => x_return_status);
p_create_update_flag = 'C'
OR
(
p_create_update_flag = 'U'
AND
p_person_rec.rent_own_ind <> p_old_person_rec.rent_own_ind
)
)
THEN
validate_lookup (
p_column => 'rent_own_ind',
p_lookup_type => 'OWN_RENT_IND',
p_column_value => p_person_rec.rent_own_ind,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
IF p_person_rec.date_of_birth IS NOT NULL then
temp_date_of_birth := p_person_rec.date_of_birth;
ELSIF p_create_update_flag = 'C' THEN
temp_date_of_birth := p_person_rec.date_of_birth;
IF p_create_update_flag = 'U' THEN
IF p_person_rec.date_of_death IS NOT NULL then
temp_date_of_death := p_person_rec.date_of_death;
ELSIF p_create_update_flag = 'C' THEN
temp_date_of_death := p_person_rec.date_of_death;
p_create_update_flag => p_create_update_flag,
p_start_date_column_name => 'date_of_birth',
p_start_date => temp_date_of_birth,
p_old_start_date => p_old_person_rec.date_of_birth,
p_end_date_column_name => 'date_of_death',
p_end_date => temp_date_of_death,
p_old_end_date => p_old_person_rec.date_of_death,
x_return_status => x_return_status
);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_person_rec.marital_status <> p_old_person_rec.marital_status
)
)
THEN
validate_lookup (
p_column => 'marital_status',
p_lookup_type => 'MARITAL_STATUS',
p_column_value => p_person_rec.marital_status,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_person_rec.created_by_module,
p_old_created_by_module => p_old_person_rec.created_by_module,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_application_id => p_person_rec.application_id,
p_old_application_id => p_old_person_rec.application_id,
x_return_status => x_return_status);
(p_create_update_flag = 'C' OR
(p_create_update_flag = 'U' AND
p_person_rec.gender <> p_old_person_rec.gender))
THEN
validate_lookup (
p_column => 'gender',
p_lookup_type => 'HZ_GENDER',
p_column_value => p_person_rec.gender,
x_return_status => x_return_status);
(p_create_update_flag = 'C' OR
(p_create_update_flag = 'U' AND
p_person_rec.person_iden_type <> p_old_person_rec.person_iden_type))
THEN
validate_lookup (
p_column => 'person_iden_type',
p_lookup_type => 'HZ_PERSON_IDEN_TYPE',
p_column_value => p_person_rec.person_iden_type,
x_return_status => x_return_status);
IF(p_create_update_flag='U')
THEN
validate_hr_security
(
p_person_rec => p_person_rec,
p_old_person_rec => p_old_person_rec,
x_return_status => x_return_status
);
* following fields are updateable only by HR:
* person_first_name
* person_last_name
* person_middle_name
* person_name_suffix
* person_previous_last_name
* person_title
* known_as
* person_first_name_phonetic
* person_last_name_phonetic
* person_name_phonetic
* If the profile option 'HZ_PROTECT_HR_PERSON_INFO' is set to 'N', then the
* following sensitive fields are updateable only by HR in addition to those
* mentioned above for the case when the profile option is set to 'Y':
* gender
* date_of_birth
* place_of_birth
* marital_status
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_person_rec Person record.
* p_old_person_rec Old person record.
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* fnd_api.g_ret_sts_error (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
* The previous validation for the same purpose was performed in the procedure
* HZ_REGISTRY_VALIDATE_V2PUB.validate_party and locked all fields against update
* as against the expected functionality of locking only the HR fields.
* (Bug Number 3099624).The previous validation has been commented out.
*
* MODIFICATION HISTORY
*
* 09-26-2003 Rajib Ranjan Borah o Created.
*
*/
PROCEDURE validate_hr_security(
p_person_rec IN HZ_PARTY_V2PUB.PERSON_REC_TYPE,
p_old_person_rec IN HZ_PARTY_V2PUB.PERSON_REC_TYPE,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
cols_updated VARCHAR2(300) := '';
cols_updated :=cols_updated||',PERSON_FIRST_NAME';
cols_updated :=cols_updated||',PERSON_LAST_NAME';
cols_updated :=cols_updated||',PERSON_MIDDLE_NAME';
cols_updated :=cols_updated||',PERSON_NAME_SUFFIX';
cols_updated :=cols_updated||',PERSON_PREVIOUS_LAST_NAME';
cols_updated :=cols_updated||',KNOWN_AS';
cols_updated :=cols_updated||',PERSON_TITLE';
cols_updated :=cols_updated||',PERSON_FIRST_NAME_PHONETIC';
cols_updated :=cols_updated||',PERSON_LAST_NAME_PHONETIC';
cols_updated :=cols_updated||',PERSON_NAME_PHONETIC';
IF(cols_updated IS NOT NULL)
THEN
cols_updated:=SUBSTR(cols_updated,2);
FND_MESSAGE.SET_TOKEN('COLUMN',cols_updated);
cols_updated :=cols_updated||',PERSON_FIRST_NAME';
cols_updated :=cols_updated||',PERSON_LAST_NAME';
cols_updated :=cols_updated||',PERSON_MIDDLE_NAME';
cols_updated :=cols_updated||',PERSON_NAME_SUFFIX';
cols_updated :=cols_updated||',PERSON_PREVIOUS_LAST_NAME';
cols_updated :=cols_updated||',KNOWN_AS';
cols_updated :=cols_updated||',PERSON_TITLE';
cols_updated :=cols_updated||',PERSON_FIRST_NAME_PHONETIC';
cols_updated :=cols_updated||',PERSON_LAST_NAME_PHONETIC';
cols_updated :=cols_updated||',PERSON_NAME_PHONETIC';
cols_updated :=cols_updated||',GENDER';
cols_updated :=cols_updated||',DATE_OF_BIRTH';
cols_updated :=cols_updated||',PLACE_OF_BIRTH';
cols_updated :=cols_updated||',MARITAL_STATUS';
IF(cols_updated IS NOT NULL)
THEN
cols_updated:=SUBSTR(cols_updated,2);
FND_MESSAGE.SET_TOKEN('COLUMN',cols_updated);
* non-updateable fields
* foreign key validations
* other validations
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_group_rec Group record.
* p_old_group_rec Old group record.
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* fnd_api.g_ret_sts_error (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Indrajit Sen o Created.
*
*/
PROCEDURE validate_group(
p_create_update_flag IN VARCHAR2,
p_group_rec IN HZ_PARTY_V2PUB.GROUP_REC_TYPE,
p_old_group_rec IN HZ_PARTY_V2PUB.GROUP_REC_TYPE,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := '';
p_create_update_flag => p_create_update_flag,
p_column => 'group_name',
p_column_value => p_group_rec.group_name,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_column => 'group_type',
p_column_value => p_group_rec.group_type,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_group_rec.created_by_module,
p_old_created_by_module => p_old_group_rec.created_by_module,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_application_id => p_group_rec.application_id,
p_old_application_id => p_old_group_rec.application_id,
x_return_status => x_return_status);
* non-updateable fields
* foreign key validations
* other validations
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_organization_rec Organization record.
* p_old_organization_rec Old organization record.
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* fnd_api.g_ret_sts_error (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Indrajit Sen o Created.
* 11-07-2001 Sisir o Bug:1999814;Added validation for sic_code_type
* has been updated and not for each record.
* 31-AUG-2004 V.Ravichandran o Bug 3853738. Commented the validation for columns
* total_emp_est_ind and emp_at_primary_adr_est_ind
* against lookup_type 'YES/NO' in validate_organization
* because these columns were validated against 2 lookups.
* 13-JAN-2005 Rajib Ranjan Borah o SSM SST Integration and Extension
* Explicit non-updateability of third party provided ceo_name
* , ceo_title, etc will not be done as update rules can be used
* for the same.
* 31-MAY-2006 Nishant Singhai o Org Name update allowed check added for Bug 5174379.
*/
PROCEDURE validate_organization(
p_create_update_flag IN VARCHAR2,
p_organization_rec IN HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE,
p_old_organization_rec IN HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_dummy VARCHAR2(1);
p_create_update_flag,
p_organization_rec,
x_return_status );
-- Update only if profile (HZ: Change Party Name) is set to Y or it is
-- not set at all. If it is set to 'N' update is not allowed.
-- Check added for Bug 5174379 on 31-May-2006 (Nishant)
-------------------------------------------------------------------------
IF p_create_update_flag = 'U' THEN
l_change_org_name_profile := fnd_profile.VALUE('AR_CHANGE_CUST_NAME');
IF (NVL(l_change_org_name_profile,'Y') = 'N') THEN -- update to party name is not allowed
IF (p_organization_rec.organization_name <> p_old_organization_rec.organization_name) THEN
fnd_message.set_name('AR', 'HZ_ORG_NAME_UPDT_NOT_ALLOWED');
p_message=>'Org Name Update Allowed Check...' ||
'x_return_status : ' || x_return_status ||
'. Profile AR_CHANGE_CUST_NAME value :'||l_change_org_name_profile,
p_msg_level=>fnd_log.level_statement);
END IF; -- create update flag = U
p_create_update_flag => p_create_update_flag,
p_column => 'organization_name',
p_column_value => p_organization_rec.organization_name,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_cannot_update_to_null (
p_column => 'organization_name',
p_column_value => p_organization_rec.organization_name,
x_return_status => x_return_status);
'organization_name cannot be updated to null. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'organization_name cannot be updated to null. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_organization_rec.sic_code_type <> p_old_organization_rec.sic_code_type
)
)
THEN
validate_lookup (
p_column => 'sic_code_type',
p_lookup_type => 'SIC_CODE_TYPE',
p_column_value => p_organization_rec.sic_code_type,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_organization_rec.fiscal_yearend_month <> p_old_organization_rec.fiscal_yearend_month
)
)
THEN
validate_lookup (
p_column => 'fiscal_yearend_month',
p_lookup_type => 'MONTH',
p_column_value => p_organization_rec.fiscal_yearend_month,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_organization_rec.legal_status <> p_old_organization_rec.legal_status
)
)
THEN
validate_lookup (
p_column => 'legal_status',
p_lookup_type => 'LEGAL_STATUS',
p_column_value => p_organization_rec.legal_status,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_organization_rec.hq_branch_ind <> p_old_organization_rec.hq_branch_ind
)
)
THEN
validate_lookup (
p_column => 'hq_branch_ind',
p_lookup_type => 'HQ_BRANCH_IND',
p_column_value => p_organization_rec.hq_branch_ind,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_organization_rec.failure_score_commentary <> p_old_organization_rec.failure_score_commentary
)
)
THEN
validate_lookup (
p_column => 'failure_score_commentary',
p_lookup_type => 'FAILURE_SCORE_COMMENTARY',
p_column_value => p_organization_rec.failure_score_commentary,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_organization_rec.credit_score_commentary <> p_old_organization_rec.credit_score_commentary
)
)
THEN
validate_lookup (
p_column => 'credit_score_commentary',
p_lookup_type => 'CREDIT_SCORE_COMMENTARY',
p_column_value => p_organization_rec.credit_score_commentary,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_organization_rec.local_bus_iden_type <> p_old_organization_rec.local_bus_iden_type
)
)
THEN
validate_lookup (
p_column => 'local_bus_iden_type',
p_lookup_type => 'LOCAL_BUS_IDEN_TYPE',
p_column_value => p_organization_rec.local_bus_iden_type,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_organization_rec.registration_type <> p_old_organization_rec.registration_type
)
)
THEN
validate_lookup (
p_column => 'registration_type',
p_lookup_type => 'REGISTRATION_TYPE',
p_column_value => p_organization_rec.registration_type,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_organization_rec.local_activity_code_type <> p_old_organization_rec.local_activity_code_type
)
)
THEN
validate_lookup (
p_column => 'local_activity_code_type',
p_lookup_type => 'LOCAL_ACTIVITY_CODE_TYPE',
p_column_value => p_organization_rec.local_activity_code_type,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_organization_rec.local_activity_code <> p_old_organization_rec.local_activity_code
)
)
THEN
l_local_activity_code_type := nvl(p_organization_rec.local_activity_code_type, p_old_organization_rec.local_activity_code_type);
SELECT 'Y'
INTO l_dummy
FROM FND_CURRENCIES
WHERE CURRENCY_CODE = p_organization_rec.maximum_credit_currency_code
AND CURRENCY_FLAG = 'Y'
AND ENABLED_FLAG in ('Y', 'N');
p_create_update_flag = 'C'
OR
(
p_create_update_flag = 'U'
AND
p_organization_rec.rent_own_ind <> p_old_organization_rec.rent_own_ind
)
)
THEN
validate_lookup (
p_column => 'rent_own_ind',
p_lookup_type => 'OWN_RENT_IND',
p_column_value => p_organization_rec.rent_own_ind,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' AND (
p_organization_rec.ceo_name IS NOT NULL OR
p_organization_rec.ceo_title IS NOT NULL OR
p_organization_rec.principal_title IS NOT NULL OR
p_organization_rec.principal_name IS NOT NULL ) AND
/*p_organization_rec.actual_content_source = 'DNB' AND
NVL(FND_PROFILE.value('HZ_UPDATE_THIRD_PARTY_DATA'), 'N') = 'N'*/
/* HZ_UTILITY_V2PUB.is_purchased_content_source(p_organization_rec.actual_content_source) = 'Y' AND
p_organization_rec.actual_content_source <> HZ_PARTY_V2PUB.G_MISS_CONTENT_SOURCE_TYPE
THEN
l_return_status := FND_API.G_RET_STS_SUCCESS;
validate_nonupdateable (
p_column => 'ceo_title',
p_column_value => p_organization_rec.ceo_title,
p_old_column_value => p_old_organization_rec.ceo_title,
x_return_status => l_return_status,
p_raise_error => 'N');
validate_nonupdateable (
p_column => 'ceo_name',
p_column_value => p_organization_rec.ceo_name,
p_old_column_value => p_old_organization_rec.ceo_name,
x_return_status => l_return_status,
p_raise_error => 'N');
validate_nonupdateable (
p_column => 'principal_title',
p_column_value => p_organization_rec.principal_title,
p_old_column_value => p_old_organization_rec.principal_title,
x_return_status => l_return_status,
p_raise_error => 'N');
validate_nonupdateable (
p_column => 'principal_name',
p_column_value => p_organization_rec.principal_name,
p_old_column_value => p_old_organization_rec.principal_name,
x_return_status => l_return_status,
p_raise_error => 'N');
If you want to identify the CEO, you should not update these column,
but rather add an appropriate Org Contact.',
p_msg_level=>fnd_log.level_statement);
SELECT 'Y'
INTO l_dummy
FROM HZ_PARTIES
WHERE PARTY_ID = p_organization_rec.displayed_duns_party_id;
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_organization_rec.created_by_module,
p_old_created_by_module => p_old_organization_rec.created_by_module,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_application_id => p_organization_rec.application_id,
p_old_application_id => p_old_organization_rec.application_id,
x_return_status => x_return_status);
* non-updateable fields
* foreign key validations
* other validations
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_party_site_rec Party site record.
* p_rowid Rowid of the record (used only in update mode).
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* fnd_api.g_ret_sts_error (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Indrajit Sen o Created.
* 04-19-2004 Rajib Ranjan Borah o Bug 3175816. If the value of GLOBAL_LOCATION_NUMBER
* has changed, then called validate_global_loc_num.
* 03-May-3004 Venkata Sowjanya S Bug No : 3609601. Commented the statements which sets tokens Column1,Column2
* for message HZ_API_INACTIVE_CANNOT_PRIM
*/
PROCEDURE validate_party_site(
p_create_update_flag IN VARCHAR2,
p_party_site_rec IN HZ_PARTY_SITE_V2PUB.PARTY_SITE_REC_TYPE,
p_rowid IN ROWID,
x_return_status IN OUT NOCOPY VARCHAR2,
x_loc_actual_content_source OUT NOCOPY VARCHAR2
) IS
l_count NUMBER;
IF p_create_update_flag = 'U' THEN
SELECT PARTY_ID,
LOCATION_ID,
PARTY_SITE_NUMBER,
ORIG_SYSTEM_REFERENCE,
IDENTIFYING_ADDRESS_FLAG,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
STATUS,
CREATED_BY_MODULE,
APPLICATION_ID,
ACTUAL_CONTENT_SOURCE,
GLOBAL_LOCATION_NUMBER
INTO l_party_id,
l_location_id,
l_party_site_number,
l_orig_system_reference,
l_identifying_address_flag,
l_start_date_active,
l_end_date_active,
l_status,
l_created_by_module,
l_application_id,
db_actual_content_source,
l_global_location_number
FROM HZ_PARTY_SITES
WHERE ROWID = p_rowid;
p_create_update_flag => p_create_update_flag,
p_column => 'party_id',
p_column_value => p_party_site_rec.party_id,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'party_id',
p_column_value => p_party_site_rec.party_id,
p_old_column_value => l_party_id,
x_return_status => x_return_status);
'party_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'party_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C'
AND
p_party_site_rec.party_id IS NOT NULL
AND
p_party_site_rec.party_id <> fnd_api.g_miss_num
AND
p_party_site_rec.party_id <> -1
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM HZ_PARTIES
WHERE PARTY_ID = p_party_site_rec.party_id;
p_create_update_flag => p_create_update_flag,
p_column => 'location_id',
p_column_value => p_party_site_rec.location_id,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'location_id',
p_column_value => p_party_site_rec.location_id,
p_old_column_value => l_location_id,
x_return_status => x_return_status);
'location_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'location_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C'
AND
p_party_site_rec.location_id IS NOT NULL
AND
p_party_site_rec.location_id <> fnd_api.g_miss_num
THEN
BEGIN
-- Bug 2197181: for mix-n-match, column actual_content_source
-- was added to hz_party_sites. It is denormalized from hz_locations.
-- Therefore, it is selected from hz_locations to be passed back
-- to create_party_site API.
SELECT actual_content_source
INTO x_loc_actual_content_source
FROM HZ_LOCATIONS
WHERE LOCATION_ID = p_party_site_rec.location_id;
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'party_site_number',
p_column_value => p_party_site_rec.party_site_number,
p_old_column_value => l_party_site_number,
x_return_status => x_return_status);
'party_site_number is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'party_site_number is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
and p_create_update_flag = 'U'
then
hz_orig_system_ref_pub.get_owner_table_id
(p_orig_system => p_party_site_rec.orig_system,
p_orig_system_reference => p_party_site_rec.orig_system_reference,
p_owner_table_name => 'HZ_PARTY_SITES',
x_owner_table_id => l_mosr_owner_table_id,
x_return_status => l_temp_return_status);
IF p_create_update_flag = 'U' and l_validate_osr = 'Y' THEN
validate_nonupdateable (
p_column => 'orig_system_reference',
p_column_value => p_party_site_rec.orig_system_reference,
p_old_column_value => l_orig_system_reference,
x_return_status => x_return_status);
'orig_system_reference is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'orig_system_reference is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'U' THEN
validate_cannot_update_to_null (
p_column => 'status',
p_column_value => p_party_site_rec.status,
x_return_status => x_return_status);
'status cannot be set to null during update. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'status cannot be set to null during update. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_party_site_rec.status <> NVL(l_status, fnd_api.g_miss_char)
)
)
THEN
validate_lookup (
p_column => 'status',
p_lookup_type => 'REGISTRY_STATUS',
p_column_value => p_party_site_rec.status,
x_return_status => x_return_status);
AND (p_create_update_flag = 'C'
OR (p_create_update_flag = 'U'
AND p_party_site_rec.identifying_address_flag <>
NVL(l_identifying_address_flag, fnd_api.g_miss_char)))
THEN
SELECT DECODE(p_party_site_rec.identifying_address_flag,
'Y', DECODE(p_party_site_rec.status,
'I', 'N',
'', DECODE(l_status, 'I', 'N')),
'Y')
INTO l_dummy
FROM dual;
SELECT 'Y'
INTO l_dummy
FROM FND_LANGUAGES
WHERE LANGUAGE_CODE = p_party_site_rec.language
AND INSTALLED_FLAG IN ('B', 'I');
p_create_update_flag = 'C' OR
(
p_create_update_flag = 'U' AND
p_party_site_rec.global_location_number <> l_global_location_number
)
)
THEN
validate_global_loc_num(
global_location_number => p_party_site_rec.global_location_number,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_party_site_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_party_site_rec.application_id,
p_old_application_id => l_application_id,
x_return_status => x_return_status);
* non-updateable fields
* foreign key validations
* other validations
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_party_site_use_rec Party site use record.
* p_rowid Rowid of the record (used only in update mode).
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* fnd_api.g_ret_sts_error (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Indrajit Sen o Created.
* 11-05-2003 Rajib Ranjan Borah o Bug 2065191.Primary_per_type cannot be set to 'Y'
* if status is not 'A'.
* Added variable l_primary_per_type.
* 03-May-3004 Venkata Sowjanya S Bug No : 3609601. Commented the statements which sets tokens Column1,Column2
* for message HZ_API_INACTIVE_CANNOT_PRIM
*/
PROCEDURE validate_party_site_use(
p_create_update_flag IN VARCHAR2,
p_party_site_use_rec IN HZ_PARTY_SITE_V2PUB.PARTY_SITE_USE_REC_TYPE,
p_rowid IN ROWID,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_count NUMBER;
IF p_create_update_flag = 'U' THEN
SELECT PARTY_SITE_ID,
SITE_USE_TYPE,
BEGIN_DATE,
STATUS,
CREATED_BY_MODULE,
APPLICATION_ID,
--Bug 2065191
PRIMARY_PER_TYPE
INTO l_party_site_id,
l_site_use_type,
l_begin_date,
l_status,
l_created_by_module,
l_application_id,
--BUG 2065191
l_primary_per_type
FROM HZ_PARTY_SITE_USES
WHERE ROWID = p_rowid;
p_create_update_flag => p_create_update_flag,
p_column => 'party_site_id',
p_column_value => p_party_site_use_rec.party_site_id,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'party_site_id',
p_column_value => p_party_site_use_rec.party_site_id,
p_old_column_value => l_party_site_id,
x_return_status => x_return_status);
'party_site_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'party_site_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C'
AND
p_party_site_use_rec.party_site_id IS NOT NULL
AND
p_party_site_use_rec.party_site_id <> fnd_api.g_miss_num
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM HZ_PARTY_SITES
WHERE PARTY_SITE_ID = p_party_site_use_rec.party_site_id;
p_create_update_flag => p_create_update_flag,
p_column => 'site_use_type',
p_column_value => p_party_site_use_rec.site_use_type,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'site_use_type',
p_column_value => p_party_site_use_rec.site_use_type,
p_old_column_value => l_site_use_type,
x_return_status => x_return_status);
'site_use_type is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'site_use_type is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
SELECT 'Y'
INTO l_dummy
FROM HZ_PARTY_SITE_USES
-- Bug 3988537.
WHERE PARTY_SITE_ID = nvl(p_party_site_use_rec.party_site_id,l_party_site_id)
AND SITE_USE_TYPE = nvl(p_party_site_use_rec.site_use_type,l_site_use_type)
AND STATUS = 'A'
AND PARTY_SITE_USE_ID <> NVL(p_party_site_use_rec.party_site_use_id, fnd_api.g_miss_num);
IF p_create_update_flag = 'U' THEN
validate_cannot_update_to_null (
p_column => 'status',
p_column_value => p_party_site_use_rec.status,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_party_site_use_rec.status <> NVL(l_status, fnd_api.g_miss_char)
)
)
THEN
validate_lookup (
p_column => 'status',
p_lookup_type => 'REGISTRY_STATUS',
p_column_value => p_party_site_use_rec.status,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_party_site_use_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_party_site_use_rec.application_id,
p_old_application_id => l_application_id,
x_return_status => x_return_status);
* non-updateable fields
* foreign key validations
* other validations
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_org_contact_rec Org contact record.
* p_rowid Rowid of the record (used only in update mode).
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* fnd_api.g_ret_sts_error (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Indrajit Sen o Created.
*
*/
PROCEDURE validate_org_contact(
p_create_update_flag IN VARCHAR2,
p_org_contact_rec IN HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_REC_TYPE,
p_rowid IN ROWID,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_party_relationship_id NUMBER;
IF p_create_update_flag = 'U' THEN
SELECT PARTY_RELATIONSHIP_ID,
-- TITLE,
JOB_TITLE_CODE,
DEPARTMENT_CODE,
CREATED_BY_MODULE,
APPLICATION_ID
INTO l_party_relationship_id,
-- l_title,
l_job_title_code,
l_department_code,
l_created_by_module,
l_application_id
FROM HZ_ORG_CONTACTS
WHERE ROWID = p_rowid;
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'party_relationship_id',
p_column_value => p_org_contact_rec.party_rel_rec.relationship_id,
p_old_column_value => l_party_relationship_id,
x_return_status => x_return_status);
'party_relationship_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'party_relationship_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_org_contact_rec.title <> NVL(l_title, fnd_api.g_miss_char)
)
)
THEN
validate_lookup (
p_column => 'title',
p_lookup_type => 'CONTACT_TITLE',
p_column_value => p_org_contact_rec.title,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_org_contact_rec.job_title_code <> NVL(l_job_title_code, fnd_api.g_miss_char)
)
)
THEN
validate_lookup (
p_column => 'job_title_code',
p_lookup_type => 'RESPONSIBILITY',
p_column_value => p_org_contact_rec.job_title_code,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_org_contact_rec.department_code <> NVL(l_department_code, fnd_api.g_miss_char)
)
)
THEN
validate_lookup (
p_column => 'department_code',
p_lookup_type => 'DEPARTMENT_TYPE',
p_column_value => p_org_contact_rec.department_code,
x_return_status => x_return_status);
SELECT 'Y'
INTO l_dummy
FROM HZ_PARTY_SITES
WHERE PARTY_SITE_ID = p_org_contact_rec.party_site_id;
SELECT 'Y'
INTO l_dummy
FROM HZ_PARTY_SITES
WHERE PARTY_SITE_ID = p_org_contact_rec.party_site_id
AND PARTY_ID = p_org_contact_rec.party_rel_rec.object_id;
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_org_contact_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_org_contact_rec.application_id,
p_old_application_id => l_application_id,
x_return_status => x_return_status);
* non-updateable fields
* foreign key validations
* other validations
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_org_contact_role_rec Org contact role record.
* p_rowid Rowid of the record (used only in update mode).
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* fnd_api.g_ret_sts_error (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Indrajit Sen o Created.
*
*/
PROCEDURE validate_org_contact_role(
p_create_update_flag IN VARCHAR2,
p_org_contact_role_rec IN HZ_PARTY_CONTACT_V2PUB.ORG_CONTACT_ROLE_REC_TYPE,
p_rowid IN ROWID,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_org_contact_id NUMBER;
IF p_create_update_flag = 'U' THEN
SELECT ORG_CONTACT_ID,
ORIG_SYSTEM_REFERENCE,
STATUS,
CREATED_BY_MODULE,
APPLICATION_ID
INTO l_org_contact_id,
l_orig_system_reference,
l_status,
l_created_by_module,
l_application_id
FROM HZ_ORG_CONTACT_ROLES
WHERE ROWID = p_rowid;
p_create_update_flag => p_create_update_flag,
p_column => 'org_contact_id',
p_column_value => p_org_contact_role_rec.org_contact_id,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'org_contact_id',
p_column_value => p_org_contact_role_rec.org_contact_id,
p_old_column_value => l_org_contact_id,
x_return_status => x_return_status);
'org_contact_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'org_contact_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C'
AND
p_org_contact_role_rec.org_contact_id IS NOT NULL
AND p_org_contact_role_rec.org_contact_id <> fnd_api.g_miss_num
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM HZ_ORG_CONTACTS
WHERE ORG_CONTACT_ID = p_org_contact_role_rec.org_contact_id;
and p_create_update_flag = 'U'
then
hz_orig_system_ref_pub.get_owner_table_id
(p_orig_system => p_org_contact_role_rec.orig_system,
p_orig_system_reference => p_org_contact_role_rec.orig_system_reference,
p_owner_table_name => 'HZ_ORG_CONTACT_ROLES',
x_owner_table_id => l_mosr_owner_table_id,
x_return_status => l_temp_return_status);
IF p_create_update_flag = 'U' and l_validate_osr = 'Y' THEN
validate_nonupdateable (
p_column => 'orig_system_reference',
p_column_value => p_org_contact_role_rec.orig_system_reference,
p_old_column_value => l_orig_system_reference,
x_return_status => x_return_status);
'orig_system_reference is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
p_message=>'orig_system_reference is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
p_create_update_flag => p_create_update_flag,
p_column => 'role_type',
p_column_value => p_org_contact_role_rec.role_type,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_cannot_update_to_null (
p_column => 'role_type',
p_column_value => p_org_contact_role_rec.role_type,
x_return_status => x_return_status);
'role_type cannot be set to null during update. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
p_message=>'role_type cannot be set to null during update. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
SELECT ORG_CONTACT_ROLE_ID
INTO l_role_id
FROM HZ_ORG_CONTACT_ROLES
WHERE ORG_CONTACT_ID = p_org_contact_role_rec.org_contact_id
AND PRIMARY_FLAG = 'Y'
AND ROWNUM = 1;
p_create_update_flag = 'C' THEN
fnd_message.set_name('AR', 'HZ_API_UNIQUE_PRIMARY_ROLE');
SELECT ORG_CONTACT_ROLE_ID
INTO l_role_id
FROM HZ_ORG_CONTACT_ROLES
WHERE ORG_CONTACT_ID = NVL(p_org_contact_role_rec.org_contact_id, fnd_api.g_miss_num)
AND ROLE_TYPE = p_org_contact_role_rec.role_type
AND STATUS = 'A' -- Added: Bug#6411541
AND ROWNUM = 1;
SELECT 'Y'
INTO l_dummy
FROM HZ_RELATIONSHIPS PR,
HZ_ORG_CONTACTS OC,
HZ_ORG_CONTACT_ROLES OCR,
HZ_RELATIONSHIPS PR2,
HZ_ORG_CONTACTS OC2
WHERE OCR.PRIMARY_CONTACT_PER_ROLE_TYPE = 'Y'
AND OCR.ROLE_TYPE = p_org_contact_role_rec.role_type
AND OCR.ORG_CONTACT_ID = OC.ORG_CONTACT_ID
AND OC.PARTY_RELATIONSHIP_ID = PR.RELATIONSHIP_ID
AND PR.OBJECT_ID = PR2.OBJECT_ID
AND PR2.RELATIONSHIP_ID = OC2.PARTY_RELATIONSHIP_ID
AND OC2.ORG_CONTACT_ID = P_ORG_CONTACT_ROLE_REC.ORG_CONTACT_ID
AND PR.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PR.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PR.DIRECTIONAL_FLAG = 'F'
AND PR2.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PR2.OBJECT_TABLE_NAME = 'HZ_PARTIES'
AND PR2.DIRECTIONAL_FLAG = 'F'
and ocr.org_contact_role_id <>p_org_contact_role_rec.org_contact_role_id; --db primary role id is not same as the pass in role id
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_org_contact_role_rec.status <> NVL(l_status, fnd_api.g_miss_char)
)
)
THEN
validate_lookup (
p_column => 'status',
p_lookup_type => 'REGISTRY_STATUS',
p_column_value => p_org_contact_role_rec.status,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_cannot_update_to_null (
p_column => 'status',
p_column_value => p_org_contact_role_rec.status,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_org_contact_role_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_org_contact_role_rec.application_id,
p_old_application_id => l_application_id,
x_return_status => x_return_status);
* non-updateable fields
* foreign key validations
* other validations
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_person_language_rec Person language record.
* p_rowid Rowid of the record (used only in update mode).
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* fnd_api.g_ret_sts_error (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Indrajit Sen o Created.
* 01-10-2003 Porkodi Chinnandar o Added few lines to have validation for spoken_
* comprehension_level. The value entered for spoken_
* comprehension_level is validated against lookup type
* HZ_LANGUAGE_PROFICIENCY.
* 18-03-2003 Porkodi Chinnandar o 2820135, Party should have only one native_languge and
* one primary language. This check has been moved to
* ARH2PISB due to the update involved in this.
* 01-MAR-2004 Rajib Ranjan Borah o Bug 3363458.Modified previous validation to ensure
* that the primary language cannot be inactive.
* Added local variable l_primary_language_indicator
* for the same.
* 03-May-3004 Venkata Sowjanya S Bug No : 3609601. Commented the statements which sets tokens Column1,Column2
* for message HZ_API_INACTIVE_CANNOT_PRIM
*/
PROCEDURE validate_person_language(
p_create_update_flag IN VARCHAR2,
p_person_language_rec IN HZ_PERSON_INFO_V2PUB.PERSON_LANGUAGE_REC_TYPE,
p_rowid IN ROWID ,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_count NUMBER;
IF p_create_update_flag = 'U'
THEN
SELECT PARTY_ID,
LANGUAGE_NAME,
STATUS,
CREATED_BY_MODULE,
APPLICATION_ID,
PRIMARY_LANGUAGE_INDICATOR
INTO l_party_id,
l_language_name,
l_status,
l_created_by_module,
l_application_id,
l_primary_language_indicator
FROM HZ_PERSON_LANGUAGE
WHERE ROWID = p_rowid;
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'party_id',
p_column_value => p_person_language_rec.party_id,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'party_id',
p_column_value => p_person_language_rec.party_id,
p_old_column_value => l_party_id,
x_return_status => x_return_status);
'party_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'party_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C'
AND
p_person_language_rec.party_id IS NOT NULL
AND
p_person_language_rec.party_id <> fnd_api.g_miss_num
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM hz_parties
WHERE party_id = p_person_language_rec.party_id;
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'language_name',
p_column_value => p_person_language_rec.language_name,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'language_name',
p_column_value => p_person_language_rec.language_name,
p_old_column_value => l_language_name,
x_return_status => x_return_status);
'language_name is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'language_name is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
SELECT 'Y'
INTO l_dummy
FROM FND_LANGUAGES
WHERE LANGUAGE_CODE = p_person_language_rec.language_name;
SELECT 1
INTO l_dummy
FROM HZ_PERSON_LANGUAGE
WHERE PARTY_ID = l_party_id
AND LANGUAGE_NAME = p_person_language_rec.language_name
AND LANGUAGE_USE_REFERENCE_ID <> NVL(p_person_language_rec.language_use_reference_id, fnd_api.g_miss_num);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_person_language_rec.status <> NVL(l_status, fnd_api.g_miss_char)
)
)
THEN
validate_lookup (
p_column => 'status',
p_lookup_type => 'REGISTRY_STATUS',
p_column_value => p_person_language_rec.status,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_cannot_update_to_null (
p_column => 'status',
p_column_value => p_person_language_rec.status,
x_return_status => x_return_status);
AND (p_create_update_flag = 'C'
OR (p_create_update_flag = 'U'
AND p_person_language_rec.primary_language_indicator <>
NVL(l_primary_language_indicator, fnd_api.g_miss_char)))
THEN
SELECT DECODE(p_person_language_rec.primary_language_indicator,
'Y', DECODE(p_person_language_rec.status,
'I', 'N',
'', DECODE(l_status, 'I', 'N')),
'Y')
INTO l_dummy
FROM dual;
IF p_create_update_flag = 'C'
THEN
IF (p_person_language_rec.primary_language_indicator = 'Y'
AND p_person_language_rec.status = 'I')
THEN
fnd_message.set_name('AR', 'HZ_API_INACTIVE_CANNOT_PRIM');
ELSE -- p_create_update_flag = 'U'
IF (NVL(p_person_language_rec.primary_language_indicator,l_primary_language_indicator)='Y'
AND NVL(p_person_language_rec.status,l_status)='I')
THEN
fnd_message.set_name('AR', 'HZ_API_INACTIVE_CANNOT_PRIM');
END IF; -- corresponding to IF p_create_update_flag = 'C'
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_person_language_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_person_language_rec.application_id,
p_old_application_id => l_application_id,
x_return_status => x_return_status);
* non-updateable fields
* foreign key validations
* other validations
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_citizenship_rec Citizenship record.
* p_rowid Rowid of the record (used only in update mode).
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* fnd_api.g_ret_sts_error (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 31-Jan-2001 Porkodi C o Created.
* 10-Mar-2003 Porkodi C o Bug 2820483, Added mandatory check for country_code
* Bug 2820462, Added the party_type check for the party_id
*
*/
PROCEDURE validate_citizenship(
p_create_update_flag IN VARCHAR2,
p_citizenship_rec IN HZ_PERSON_INFO_V2PUB.CITIZENSHIP_REC_TYPE,
p_rowid IN ROWID ,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_count NUMBER;
SELECT 'Y'
FROM hz_citizenship hc
WHERE hc.citizenship_id = p_citizenship_id;
IF p_create_update_flag = 'U'
THEN
SELECT CITIZENSHIP_ID,
PARTY_ID,
STATUS,
CREATED_BY_MODULE,
APPLICATION_ID
INTO l_citizenship_id,
l_party_id,
l_status,
l_created_by_module,
l_application_id
FROM HZ_CITIZENSHIP
WHERE ROWID = p_rowid;
IF p_create_update_flag = 'C' THEN
IF p_citizenship_rec.citizenship_id IS NOT NULL AND
p_citizenship_rec.citizenship_id <> fnd_api.g_miss_num
THEN
OPEN citizen_cur (p_citizenship_rec.citizenship_id);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'citizenship_id',
p_column_value => p_citizenship_rec.citizenship_id,
p_old_column_value => l_citizenship_id,
x_return_status => x_return_status);
'citizenship_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'citizenship_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'party_id',
p_column_value => p_citizenship_rec.party_id,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'party_id',
p_column_value => p_citizenship_rec.party_id,
p_old_column_value => l_party_id,
x_return_status => x_return_status);
'party_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'party_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C'
AND
p_citizenship_rec.party_id IS NOT NULL
AND
p_citizenship_rec.party_id <> fnd_api.g_miss_num
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM hz_parties p
WHERE p.party_type='PERSON' and
p.party_id = p_citizenship_rec.party_id;
p_column => 'birth_or_selected',
p_lookup_type => 'HZ_CITIZENSHIP_ACQUISITION',
p_column_value => p_citizenship_rec.birth_or_selected,
x_return_status => x_return_status);
'birth_or_selected should be in lookup HZ_CITIZENSHIP_ACQUISITION. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
p_message=>'birth_or_selected should be in lookup HZ_CITIZENSHIP_ACQUISITION. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'country_code',
p_column_value => p_citizenship_rec.country_code,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_cannot_update_to_null (
p_column => 'country_code',
p_column_value => p_citizenship_rec.country_code,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_citizenship_rec.status <> NVL(l_status, fnd_api.g_miss_char)
)
)
THEN
validate_lookup (
p_column => 'status',
p_lookup_type => 'REGISTRY_STATUS',
p_column_value => p_citizenship_rec.status,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_cannot_update_to_null (
p_column => 'status',
p_column_value => p_citizenship_rec.status,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_citizenship_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_citizenship_rec.application_id,
p_old_application_id => l_application_id,
x_return_status => x_return_status);
* non-updateable fields
* foreign key validations
* other validations
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_education_rec Education record.
* p_rowid Rowid of the record (used only in update mode).
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* fnd_api.g_ret_sts_error (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 31-Jan-2001 Porkodi C o Created.
* 11-Mar-2003 Porkodi C o 2820602: Changed the error message, when wrong
* party_type has been passed to the user.
* 07-Apr-2003 Porkodi C o 2888486: On update mutual exclusivity between
* school_party_id and school_attended_name was not
* been maintained.
* 09-Apr-2003 Porkodi C o 2888399: validation for start_date_attended and last_date_attended
* has been modified
* 17-Feb-2004 Rajib Ranjan B o Bug 3425871.Type_of_school will be validated only
* if the column value changes.
*/
PROCEDURE validate_education(
p_create_update_flag IN VARCHAR2,
p_education_rec IN HZ_PERSON_INFO_V2PUB.EDUCATION_REC_TYPE,
p_rowid IN ROWID ,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_count NUMBER;
SELECT 'Y'
FROM hz_education hc
WHERE hc.education_id = p_education_id;
IF p_create_update_flag = 'U'
THEN
SELECT EDUCATION_ID,
PARTY_ID,
START_DATE_ATTENDED,
LAST_DATE_ATTENDED,
STATUS,
CREATED_BY_MODULE,
APPLICATION_ID,
SCHOOL_PARTY_ID,
SCHOOL_ATTENDED_NAME,
TYPE_OF_SCHOOL
INTO l_education_id,
l_party_id,
l_start_date_attended,
l_last_date_attended,
l_status,
l_created_by_module,
l_application_id,
l_school_party_id,
l_school_attended_name,
l_type_of_school
FROM HZ_EDUCATION
WHERE ROWID = p_rowid;
IF p_create_update_flag = 'C' THEN
IF p_education_rec.education_id IS NOT NULL AND
p_education_rec.education_id <> fnd_api.g_miss_num
THEN
OPEN education_cur (p_education_rec.education_id);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'education_id',
p_column_value => p_education_rec.education_id,
p_old_column_value => l_education_id,
x_return_status => x_return_status);
'education_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'education_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'party_id',
p_column_value => p_education_rec.party_id,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'party_id',
p_column_value => p_education_rec.party_id,
p_old_column_value => l_party_id,
x_return_status => x_return_status);
'party_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'party_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C'
AND
p_education_rec.party_id IS NOT NULL
AND
p_education_rec.party_id <> fnd_api.g_miss_num
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM hz_parties p
WHERE p.party_id = p_education_rec.party_id and
party_type = 'PERSON';
IF p_create_update_flag = 'U' THEN
IF p_education_rec.start_date_attended IS NOT NULL then
temp_start_date_attended := p_education_rec.start_date_attended;
ELSIF p_create_update_flag = 'C' THEN
temp_start_date_attended := p_education_rec.start_date_attended;
IF p_create_update_flag = 'U' THEN
IF p_education_rec.last_date_attended IS NOT NULL then
temp_last_date_attended := p_education_rec.last_date_attended;
ELSIF p_create_update_flag = 'C' THEN
temp_last_date_attended := p_education_rec.last_date_attended;
p_create_update_flag => p_create_update_flag,
p_start_date_column_name => 'start_date_attended',
p_start_date => p_education_rec.start_date_attended,
p_old_start_date => l_start_date_attended,
p_end_date_column_name => 'last_date_attended',
p_end_date => p_education_rec.last_date_attended,
p_old_end_date => l_last_date_attended,
x_return_status => x_return_status
);
IF p_create_update_flag = 'U' THEN
IF p_education_rec.school_party_id IS NOT NULL then
temp_school_party_id := p_education_rec.school_party_id;
ELSIF p_create_update_flag = 'C' THEN
temp_school_party_id := p_education_rec.school_party_id;
IF p_create_update_flag = 'U' THEN
IF p_education_rec.school_attended_name IS NOT NULL then
temp_school_attended_name := p_education_rec.school_attended_name;
ELSIF p_create_update_flag = 'C' THEN
temp_school_attended_name := p_education_rec.school_attended_name;
SELECT 'Y'
INTO l_dummy
FROM HZ_PARTIES
WHERE PARTY_ID = p_education_rec.school_party_id AND
PARTY_TYPE= 'ORGANIZATION';
p_create_update_flag = 'C'
OR
(
p_create_update_flag = 'U'
AND
p_education_rec.type_of_school <> l_type_of_school
)
)
THEN
validate_lookup (
p_column => 'type_of_school',
p_lookup_type => 'HZ_TYPE_OF_SCHOOL',
p_column_value => p_education_rec.type_of_school,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_education_rec.status <> NVL(l_status, fnd_api.g_miss_char)
)
)
THEN
validate_lookup (
p_column => 'status',
p_lookup_type => 'REGISTRY_STATUS',
p_column_value => p_education_rec.status,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_cannot_update_to_null (
p_column => 'status',
p_column_value => p_education_rec.status,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_education_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_education_rec.application_id,
p_old_application_id => l_application_id,
x_return_status => x_return_status);
* non-updateable fields
* foreign key validations
* other validations
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_employment_history_rec Employment_history record.
* p_rowid Rowid of the record (used only in update mode).
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* fnd_api.g_ret_sts_error (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 31-Jan-2001 Porkodi C o Created.
* 11-Mar-2003 Porkodi C o 2829037, Changed the code to update values for tenure_code and
* fraction_of_tenure, while the value for faculty_position_flag is
* is null.
* 09-Apr-2003 Porkodi C o 2890662, validation for begin_date and end_date combination has
* been modified.
* 25-Oct-2005 Jayashree K o 3848056 Validation for weekly hours to hold the value 0
*/
PROCEDURE validate_employment_history(
p_create_update_flag IN VARCHAR2,
p_employment_history_rec IN HZ_PERSON_INFO_V2PUB.EMPLOYMENT_HISTORY_REC_TYPE,
p_rowid IN ROWID ,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_count NUMBER;
SELECT 'Y'
FROM hz_employment_history hc
WHERE hc.employment_history_id = p_employment_history_id;
IF p_create_update_flag = 'U'
THEN
SELECT EMPLOYMENT_HISTORY_ID,
PARTY_ID,
BEGIN_DATE,
END_DATE,
FACULTY_POSITION_FLAG,
EMPLOYED_BY_PARTY_ID,
EMPLOYED_BY_NAME_COMPANY,
EMPLOYED_AS_TITLE_CODE,
EMPLOYED_AS_TITLE,
STATUS,
CREATED_BY_MODULE,
APPLICATION_ID
INTO l_employment_history_id,
l_party_id,
l_begin_date,
l_end_date,
l_faculty_position_flag,
l_employed_by_party_id,
l_employed_by_name_company,
l_employed_as_title_code,
l_employed_as_title,
l_status,
l_created_by_module,
l_application_id
FROM HZ_EMPLOYMENT_HISTORY
WHERE ROWID = p_rowid;
IF p_create_update_flag = 'C' THEN
IF p_employment_history_rec.employment_history_id IS NOT NULL AND
p_employment_history_rec.employment_history_id <> fnd_api.g_miss_num
THEN
OPEN employment_history_cur (p_employment_history_rec.employment_history_id);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'employment_history_id',
p_column_value => p_employment_history_rec.employment_history_id,
p_old_column_value => l_employment_history_id,
x_return_status => x_return_status);
'employment_history_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
p_message=>'employment_history_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'party_id',
p_column_value => p_employment_history_rec.party_id,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'party_id',
p_column_value => p_employment_history_rec.party_id,
p_old_column_value => l_party_id,
x_return_status => x_return_status);
'party_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'party_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C'
AND
p_employment_history_rec.party_id IS NOT NULL
AND
p_employment_history_rec.party_id <> fnd_api.g_miss_num
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM hz_parties p
WHERE p.party_id = p_employment_history_rec.party_id and
party_type = 'PERSON';
IF p_create_update_flag = 'U' THEN
IF p_employment_history_rec.begin_date IS NOT NULL then
temp_begin_date := p_employment_history_rec.begin_date;
ELSIF p_create_update_flag = 'C' THEN
temp_begin_date := p_employment_history_rec.begin_date;
IF p_create_update_flag = 'U' THEN
IF p_employment_history_rec.end_date IS NOT NULL then
temp_end_date := p_employment_history_rec.end_date;
ELSIF p_create_update_flag = 'C' THEN
temp_end_date := p_employment_history_rec.end_date;
p_create_update_flag => p_create_update_flag,
p_start_date_column_name => 'begin_date',
p_start_date => p_employment_history_rec.begin_date,
p_old_start_date => l_begin_date,
p_end_date_column_name => 'end_date',
p_end_date => p_employment_history_rec.end_date,
p_old_end_date => l_end_date,
x_return_status => x_return_status
);
IF p_create_update_flag = 'U' THEN
IF p_employment_history_rec.employed_as_title_code IS NOT NULL then
temp_employed_as_title_code := p_employment_history_rec.employed_as_title_code;
ELSIF p_create_update_flag = 'C' THEN
temp_employed_as_title_code := p_employment_history_rec.employed_as_title_code;
IF p_create_update_flag = 'U' THEN
IF p_employment_history_rec.employed_as_title IS NOT NULL then
temp_employed_as_title := p_employment_history_rec.employed_as_title;
ELSIF p_create_update_flag = 'C' THEN
temp_employed_as_title := p_employment_history_rec.employed_as_title;
SELECT 'Y'
INTO l_dummy
FROM HZ_PARTIES
WHERE PARTY_ID = p_employment_history_rec.employed_by_party_id;
IF p_create_update_flag = 'U' THEN
IF p_employment_history_rec.employed_by_party_id IS NOT NULL then
temp_employed_by_party_id := p_employment_history_rec.employed_by_party_id;
ELSIF p_create_update_flag = 'C' THEN
temp_employed_by_party_id := p_employment_history_rec.employed_by_party_id;
IF p_create_update_flag = 'U' THEN
IF p_employment_history_rec.employed_by_name_company IS NOT NULL then
temp_employed_by_name_company := p_employment_history_rec.employed_by_name_company;
ELSIF p_create_update_flag = 'C' THEN
temp_employed_by_name_company := p_employment_history_rec.employed_by_name_company;
p_create_update_flag => p_create_update_flag,
p_column => 'faculty_position_flag',
p_column_value => p_employment_history_rec.faculty_position_flag,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
IF p_employment_history_rec.faculty_position_flag IS NOT NULL then
temp_faculty_position_flag := p_employment_history_rec.faculty_position_flag;
ELSIF p_create_update_flag = 'C' THEN
temp_faculty_position_flag := p_employment_history_rec.faculty_position_flag;
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_employment_history_rec.status <> NVL(l_status, fnd_api.g_miss_char)
)
)
THEN
validate_lookup (
p_column => 'status',
p_lookup_type => 'REGISTRY_STATUS',
p_column_value => p_employment_history_rec.status,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_cannot_update_to_null (
p_column => 'status',
p_column_value => p_employment_history_rec.status,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_employment_history_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_employment_history_rec.application_id,
p_old_application_id => l_application_id,
x_return_status => x_return_status);
* non-updateable fields
* foreign key validations
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_work_class_rec Work_class record.
* p_rowid Rowid of the record (used only in update mode).
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* fnd_api.g_ret_sts_error (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 02-Feb-2003 Porkodi C o Created.
* 10-Mar-2003 Porkodi C Bug 2829041, Corrected the validation for employment_history_id
* to be a foreign key from hz_employment_history.
*
*/
PROCEDURE validate_work_class(
p_create_update_flag IN VARCHAR2,
p_work_class_rec IN HZ_PERSON_INFO_V2PUB.WORK_CLASS_REC_TYPE,
p_rowid IN ROWID ,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_count NUMBER;
SELECT 'Y'
FROM hz_work_class hc
WHERE hc.work_class_id = p_work_class_id;
IF p_create_update_flag = 'U'
THEN
SELECT WORK_CLASS_ID,
EMPLOYMENT_HISTORY_ID,
STATUS,
CREATED_BY_MODULE,
APPLICATION_ID
INTO L_work_class_id,
l_employment_history_id,
l_status,
l_created_by_module,
l_application_id
FROM HZ_work_class
WHERE ROWID = p_rowid;
IF p_create_update_flag = 'C' THEN
IF p_work_class_rec.work_class_id IS NOT NULL AND
p_work_class_rec.work_class_id <> fnd_api.g_miss_num
THEN
OPEN work_class_cur (p_work_class_rec.work_class_id);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'work_class_id',
p_column_value => p_work_class_rec.work_class_id,
p_old_column_value => l_work_class_id,
x_return_status => x_return_status);
'work_class_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'work_class_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'employment_history_id',
p_column_value => p_work_class_rec.employment_history_id,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'employment_history_id',
p_column_value => p_work_class_rec.employment_history_id,
p_old_column_value => l_employment_history_id,
x_return_status => x_return_status);
'employment_history_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'employment_history_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C'
AND
p_work_class_rec.employment_history_id IS NOT NULL
AND
p_work_class_rec.employment_history_id <> fnd_api.g_miss_num
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM hz_employment_history p
WHERE p.employment_history_id = p_work_class_rec.employment_history_id;
p_create_update_flag => p_create_update_flag,
p_column => 'work_class_name',
p_column_value => p_work_class_rec.work_class_name,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_work_class_rec.status <> NVL(l_status, fnd_api.g_miss_char)
)
)
THEN
validate_lookup (
p_column => 'status',
p_lookup_type => 'REGISTRY_STATUS',
p_column_value => p_work_class_rec.status,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_cannot_update_to_null (
p_column => 'status',
p_column_value => p_work_class_rec.status,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_work_class_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_work_class_rec.application_id,
p_old_application_id => l_application_id,
x_return_status => x_return_status);
* non-updateable fields
* foreign key validations
* other validations
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_person_interest_rec person_interest record.
* p_rowid Rowid of the record (used only in update mode).
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* fnd_api.g_ret_sts_error (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 31-Jan-2001 Porkodi C o Created.
* 13-Jan-2004 Rajib Ranjan Borah o Bug 3282946.Person interest can be created for
* all type of parties and not necessarily for
* 'PERSON' type parties only.
*/
PROCEDURE validate_person_interest(
p_create_update_flag IN VARCHAR2,
p_person_interest_rec IN HZ_PERSON_INFO_V2PUB.person_interest_REC_TYPE,
p_rowid IN ROWID ,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_count NUMBER;
SELECT 'Y'
FROM hz_person_interest hc
WHERE hc.person_interest_id = p_person_interest_id;
IF p_create_update_flag = 'U'
THEN
SELECT person_interest_ID,
PARTY_ID,
STATUS,
CREATED_BY_MODULE,
APPLICATION_ID
INTO l_person_interest_id,
l_party_id,
l_status,
l_created_by_module,
l_application_id
FROM HZ_person_interest
WHERE ROWID = p_rowid;
IF p_create_update_flag = 'C' THEN
IF p_person_interest_rec.person_interest_id IS NOT NULL AND
p_person_interest_rec.person_interest_id <> fnd_api.g_miss_num
THEN
OPEN person_interest_cur (p_person_interest_rec.person_interest_id);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'person_interest_id',
p_column_value => p_person_interest_rec.person_interest_id,
p_old_column_value => l_person_interest_id,
x_return_status => x_return_status);
'person_interest_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'person_interest_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'party_id',
p_column_value => p_person_interest_rec.party_id,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'party_id',
p_column_value => p_person_interest_rec.party_id,
p_old_column_value => l_party_id,
x_return_status => x_return_status);
'party_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'party_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C'
AND
p_person_interest_rec.party_id IS NOT NULL
AND
p_person_interest_rec.party_id <> fnd_api.g_miss_num
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM hz_parties p
WHERE p.party_id = p_person_interest_rec.party_id; /*and
Bug 3282946. Some teams still insert into hz_person_interest
for non 'PERSON' type parties.
*/
EXCEPTION
WHEN NO_DATA_FOUND THEN
fnd_message.set_name('AR', 'HZ_API_INVALID_FK');
p_create_update_flag => p_create_update_flag,
p_column => 'interest_name',
p_column_value => p_person_interest_rec.interest_name,
x_return_status => x_return_status);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_person_interest_rec.status <> NVL(l_status, fnd_api.g_miss_char)
)
)
THEN
validate_lookup (
p_column => 'status',
p_lookup_type => 'REGISTRY_STATUS',
p_column_value => p_person_interest_rec.status,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_cannot_update_to_null (
p_column => 'status',
p_column_value => p_person_interest_rec.status,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_person_interest_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_person_interest_rec.application_id,
p_old_application_id => l_application_id,
x_return_status => x_return_status);
* non-updateable fields
* foreign key validations
* other validations
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_location_rec Location record.
* p_rowid Rowid of the record (used only in update mode).
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* fnd_api.g_ret_sts_error (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Indrajit Sen o Created.
* 06-FEB-2004 Jianying Huang o Bug 3330754: modified validate_location to
* change validation for third party records
* from row level non-updateable checking to
* attribute level checking.
* 01-03-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
* Newly created user update rules will be used
* to check update privilege.
*
*/
PROCEDURE validate_location(
p_create_update_flag IN VARCHAR2,
p_location_rec IN hz_location_v2pub.location_rec_type,
p_rowid IN ROWID ,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_dummy VARCHAR2(1);
IF (p_create_update_flag = 'U') THEN
SELECT ADDRESS_EFFECTIVE_DATE,
CONTENT_SOURCE_TYPE,
created_by_module,
APPLICATION_ID,
actual_content_source,
-- Bug 3330754: added to support attribute level
-- non-updateable checking for third party records.
orig_system_reference,
country,
address1,
address2,
address3,
address4,
city,
postal_code,
state,
province,
county,
postal_plus4_code,
clli_code,
delivery_point_code,
location_directions
INTO l_address_effective_date,
l_content_source_type,
l_created_by_module,
l_application_id,
db_actual_content_source,
-- Bug 3330754: added to support attribute level
-- non-updateable checking for third party records.
db_orig_system_reference,
db_country,
db_address1,
db_address2,
db_address3,
db_address4,
db_city,
db_postal_code,
db_state,
db_province,
db_county,
db_postal_plus4_code,
db_clli_code,
db_delivery_point_code,
db_location_directions
FROM HZ_LOCATIONS
WHERE ROWID = p_rowid;
p_create_update_flag => p_create_update_flag,
p_column => 'address1',
p_column_value => p_location_rec.address1,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_column => 'country',
p_column_value => p_location_rec.country,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'content_source_type',
p_column_value => p_location_rec.content_source_type,
p_old_column_value => l_content_source_type,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'content_source_type is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
p_create_update_flag => p_create_update_flag,
-- Bug 3330754: replaced row level non-updateable checking to
-- attribute level checking.
p_check_update_privilege => 'N',
p_content_source_type => p_location_rec.content_source_type,
p_old_content_source_type => l_content_source_type,
p_actual_content_source => p_location_rec.actual_content_source,
p_old_actual_content_source => db_actual_content_source,
p_entity_name => 'HZ_LOCATIONS',
x_return_status => x_return_status );
IF p_create_update_flag = 'U' AND
db_actual_content_source <> 'USER_ENTERED' -- AND
-- SSM SST Integration and Extension
-- Check Updateability using mix-n-match procedure rather than checking the
-- profile value.
/*NVL(FND_PROFILE.value('HZ_UPDATE_THIRD_PARTY_DATA'), 'N') = 'N'*/
--HZ_UTILITY_V2PUB.is_purchased_content_source(db_actual_content_source) = 'Y'
THEN
l_return_status := FND_API.G_RET_STS_SUCCESS;
validate_nonupdateable (
p_column => 'orig_system_reference',
p_column_value => p_location_rec.orig_system_reference,
p_old_column_value => db_orig_system_reference,
x_return_status => l_return_status,
p_raise_error => 'N');
validate_nonupdateable (
p_column => 'country',
p_column_value => p_location_rec.country,
p_old_column_value => db_country,
x_return_status => l_return_status,
p_raise_error => 'N');
validate_nonupdateable (
p_column => 'address1',
p_column_value => p_location_rec.address1,
p_old_column_value => db_address1,
x_return_status => l_return_status,
p_raise_error => 'N');
validate_nonupdateable (
p_column => 'address2',
p_column_value => p_location_rec.address2,
p_old_column_value => db_address2,
x_return_status => l_return_status,
p_raise_error => 'N');
validate_nonupdateable (
p_column => 'address3',
p_column_value => p_location_rec.address3,
p_old_column_value => db_address3,
x_return_status => l_return_status,
p_raise_error => 'N');
validate_nonupdateable (
p_column => 'address4',
p_column_value => p_location_rec.address4,
p_old_column_value => db_address4,
x_return_status => l_return_status,
p_raise_error => 'N');
validate_nonupdateable (
p_column => 'city',
p_column_value => p_location_rec.city,
p_old_column_value => db_city,
x_return_status => l_return_status,
p_raise_error => 'N');
validate_nonupdateable (
p_column => 'postal_code',
p_column_value => p_location_rec.postal_code,
p_old_column_value => db_postal_code,
x_return_status => l_return_status,
p_raise_error => 'N');
validate_nonupdateable (
p_column => 'state',
p_column_value => p_location_rec.state,
p_old_column_value => db_state,
x_return_status => l_return_status,
p_raise_error => 'N');
validate_nonupdateable (
p_column => 'province',
p_column_value => p_location_rec.province,
p_old_column_value => db_province,
x_return_status => l_return_status,
p_raise_error => 'N');
validate_nonupdateable (
p_column => 'county',
p_column_value => p_location_rec.county,
p_old_column_value => db_county,
x_return_status => l_return_status,
p_raise_error => 'N');
validate_nonupdateable (
p_column => 'postal_plus4_code',
p_column_value => p_location_rec.postal_plus4_code,
p_old_column_value => db_postal_plus4_code,
x_return_status => l_return_status,
p_raise_error => 'N');
validate_nonupdateable (
p_column => 'clli_code',
p_column_value => p_location_rec.clli_code,
p_old_column_value => db_clli_code,
x_return_status => l_return_status,
p_raise_error => 'N');
validate_nonupdateable (
p_column => 'delivery_point_code',
p_column_value => p_location_rec.delivery_point_code,
p_old_column_value => db_delivery_point_code,
x_return_status => l_return_status,
p_raise_error => 'N');
validate_nonupdateable (
p_column => 'location_directions',
p_column_value => p_location_rec.location_directions,
p_old_column_value => db_location_directions,
x_return_status => l_return_status,
p_raise_error => 'N');
HZ_MIXNM_UTILITY.CheckUserUpdatePrivilege(
p_actual_content_source => db_actual_content_source,
p_new_actual_content_source => p_location_rec.actual_content_source,
p_entity_name => 'HZ_LOCATIONS',
x_return_status => x_return_status);
HZ_UTILITY_V2PUB.G_UPDATE_ACS := 'Y';
FND_MESSAGE.SET_NAME('AR', 'HZ_NOTALLOW_UPDATE_THIRD_PARTY');
p_message=>'third party address components are non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
SELECT 'Y'
INTO l_dummy
FROM FND_LANGUAGES
WHERE LANGUAGE_CODE = p_location_rec.language
AND INSTALLED_FLAG IN ('B', 'I');
SELECT 'Y'
INTO l_dummy
FROM HZ_TIMEZONES
WHERE TIMEZONE_ID = p_location_rec.timezone_id;
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_location_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_location_rec.application_id,
p_old_application_id => l_application_id,
x_return_status => x_return_status);
p_create_update_flag,
x_return_status);
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* fnd_api.g_ret_sts_error (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Indrajit Sen o Created.
*
*/
PROCEDURE tax_location_validation(
p_location_rec IN hz_location_v2pub.location_rec_type,
p_create_update_flag IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_location_id NUMBER;
IF p_create_update_flag = 'C' THEN
-- no validation to be done
-- tax code will be populated when customer account site is created.
null;
ELSIF p_create_update_flag = 'U' THEN
l_location_id := p_location_rec.location_id;
SELECT 'Y'
INTO l_loc_assignment_exist
FROM DUAL
WHERE EXISTS (SELECT 1
FROM hz_loc_assignments la
WHERE la.location_id = l_location_id
);
SELECT 'Y'
INTO l_is_remit_to_location
FROM DUAL
WHERE EXISTS (SELECT 1
FROM hz_party_sites ps
WHERE ps.location_id = l_location_id
AND ps.party_id = -1
);
SELECT country, city, state, county, province, postal_code,
attribute1, attribute2, attribute3, attribute4, attribute5,
attribute6, attribute7, attribute8, attribute9, attribute10
INTO o_country, o_city, o_state, o_county, o_province, o_postal_code,
o_attribute1,o_attribute2,o_attribute3,o_attribute4,o_attribute5,
o_attribute6,o_attribute7,o_attribute8,o_attribute9,o_attribute10
FROM HZ_LOCATIONS
WHERE location_id = p_location_rec.location_id ;
null; -- allow updates
END; -- end of first select
* non-updateable fields
* foreign key validations
* other validations
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_relationship_type_rec relationship type record.
* p_rowid Rowid of the record (used only in update mode).
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* fnd_api.g_ret_sts_error (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Indrajit Sen o Created.
* 12-23-2003 Rajib Ranjan Borah o Bug 2751613.Combination of relationship phrase,subject_type
* and object_type should yield an unique phrase in the reverse
* direction.
* 29-NOV-2004 S V Sowjanya o Bug 3491584: Added a validation in procedure validate_relationship_type
* to throw an error message while creating non directional relationship type
* with different subject type and object type.
*
*/
PROCEDURE validate_relationship_type(
p_create_update_flag IN VARCHAR2,
p_relationship_type_rec IN HZ_RELATIONSHIP_TYPE_V2PUB.RELATIONSHIP_TYPE_REC_TYPE,
p_rowid IN ROWID ,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_dummy VARCHAR2(1);
IF (p_create_update_flag = 'U') THEN
SELECT RELATIONSHIP_TYPE,
FORWARD_REL_CODE,
BACKWARD_REL_CODE,
DIRECTION_CODE,
HIERARCHICAL_FLAG,
CREATE_PARTY_FLAG,
ALLOW_RELATE_TO_SELF_FLAG,
ALLOW_CIRCULAR_RELATIONSHIPS,
MULTIPLE_PARENT_ALLOWED,
INCL_UNRELATED_ENTITIES,
SUBJECT_TYPE,
OBJECT_TYPE,
ROLE,
CREATED_BY_MODULE,
APPLICATION_ID
INTO l_relationship_type,
l_forward_rel_code,
l_backward_rel_code,
l_direction_code,
l_hierarchical_flag,
l_create_party_flag,
l_allow_relate_to_self_flag,
l_allow_circular_relationships,
l_multiple_parent_allowed,
l_incl_unrelated_entities,
l_subject_type,
l_object_type,
l_role,
l_created_by_module,
l_application_id
FROM HZ_RELATIONSHIP_TYPES
WHERE ROWID = p_rowid;
IF (p_create_update_flag = 'U') THEN
validate_nonupdateable (
p_column => 'ROLE',
p_column_value => p_relationship_type_rec.forward_role,
p_old_column_value => l_role,
x_return_status => x_return_status
);
SELECT ROLE INTO l_role
FROM HZ_RELATIONSHIP_TYPES
WHERE RELATIONSHIP_TYPE = l_relationship_type
AND FORWARD_REL_CODE = l_backward_rel_code
AND BACKWARD_REL_CODE = l_forward_rel_code
AND SUBJECT_TYPE = l_object_type
AND OBJECT_TYPE = l_subject_type
AND ROWNUM = 1;
validate_nonupdateable (
p_column => 'ROLE',
p_column_value => p_relationship_type_rec.backward_role,
p_old_column_value => l_role,
x_return_status => x_return_status
);
/* -- Raise an error if user tries to update forward role to NULL
IF (p_create_update_flag = 'U') THEN
validate_cannot_update_to_null (
p_column => 'role',
p_column_value => p_relationship_type_rec.forward_role,
x_return_status => x_return_status
);
IF (p_create_update_flag = 'U') THEN
validate_cannot_update_to_null (
p_column => 'role',
p_column_value => p_relationship_type_rec.backward_role,
x_return_status => x_return_status
);
IF (p_create_update_flag = 'U') THEN
validate_lookup (
p_column => 'role',
p_lookup_type => 'HZ_RELATIONSHIP_ROLE',
p_column_value => p_relationship_type_rec.forward_role,
x_return_status => x_return_status);
IF (p_create_update_flag = 'U') THEN
validate_lookup (
p_column => 'role',
p_lookup_type => 'HZ_RELATIONSHIP_ROLE',
p_column_value => p_relationship_type_rec.backward_role,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_column => 'relationship_type',
p_column_value => p_relationship_type_rec.relationship_type,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'relationship_type',
p_column_value => p_relationship_type_rec.relationship_type,
p_old_column_value => l_relationship_type,
x_return_status => x_return_status);
'relationship_type is non-updateable. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'relationship_type is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'U' THEN
validate_cannot_update_to_null (
p_column => 'status',
p_column_value => p_relationship_type_rec.status,
x_return_status => x_return_status);
'status cannot be set to null during update. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'status cannot be set to null during update. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_relationship_type_rec.status <> NVL(l_status, fnd_api.g_miss_char)
)
)
THEN
validate_lookup (
p_column => 'status',
p_lookup_type => 'CODE_STATUS',
p_column_value => p_relationship_type_rec.status,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
IF p_relationship_type_rec.create_party_flag <> fnd_api.g_miss_char
AND
p_relationship_type_rec.create_party_flag IS NOT NULL
AND
p_relationship_type_rec.create_party_flag <> l_create_party_flag
THEN
IF l_create_party_flag = 'Y' AND p_relationship_type_rec.create_party_flag = 'N' THEN
-- check if there is any relationship with this relationship type having a party.
-- if there is, do not allow the update
BEGIN
SELECT 1 INTO l_count
FROM HZ_RELATIONSHIPS
WHERE RELATIONSHIP_TYPE = l_relationship_type
AND SUBJECT_TYPE = l_subject_type
AND OBJECT_TYPE = l_object_type
AND RELATIONSHIP_CODE = l_forward_rel_code
AND PARTY_ID IS NOT NULL
AND STATUS = 'A'
AND TRUNC(SYSDATE) BETWEEN TRUNC(START_DATE) AND TRUNC(NVL(END_DATE, SYSDATE))
AND ROWNUM = 1;
fnd_message.set_name('AR', 'HZ_API_CPF_NON_UPDATEABLE');
SELECT 1 INTO l_count
FROM HZ_RELATIONSHIPS
WHERE RELATIONSHIP_TYPE = l_relationship_type
AND SUBJECT_TYPE = l_subject_type
AND OBJECT_TYPE = l_object_type
AND RELATIONSHIP_CODE = l_forward_rel_code
AND PARTY_ID IS NULL
AND STATUS = 'A'
AND TRUNC(SYSDATE) BETWEEN TRUNC(START_DATE) AND TRUNC(NVL(END_DATE, SYSDATE))
AND ROWNUM = 1;
fnd_message.set_name('AR', 'HZ_API_CPF_NON_UPDATEABLE');
'create_party_flag is updateable/non-updateable. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'create_party_flag is updateable/non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'allow_circular_relationships',
p_column_value => p_relationship_type_rec.allow_circular_relationships,
p_old_column_value => l_allow_circular_relationships,
x_return_status => x_return_status);
'allow_circular_relationships is non-updateable. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
p_message=>'allow_circular_relationships is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'allow_relate_to_self_flag',
p_column_value => p_relationship_type_rec.allow_relate_to_self_flag,
p_old_column_value => l_allow_relate_to_self_flag,
x_return_status => x_return_status);
'allow_relate_to_self_flag is non-updateable. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'allow_relate_to_self_flag is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
p_create_update_flag => p_create_update_flag,
p_column => 'forward_rel_code',
p_column_value => p_relationship_type_rec.forward_rel_code,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'forward_rel_code',
p_column_value => p_relationship_type_rec.forward_rel_code,
p_old_column_value => l_forward_rel_code,
x_return_status => x_return_status);
'forward_rel_code is non-updateable. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'forward_rel_code is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
p_create_update_flag => p_create_update_flag,
p_column => 'backward_rel_code',
p_column_value => p_relationship_type_rec.backward_rel_code,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'backward_rel_code',
p_column_value => p_relationship_type_rec.backward_rel_code,
p_old_column_value => l_backward_rel_code,
x_return_status => x_return_status);
'backward_rel_code is non-updateable. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'backward_rel_code is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
p_create_update_flag => p_create_update_flag,
p_column => 'direction_code',
p_column_value => p_relationship_type_rec.direction_code,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'direction_code',
p_column_value => p_relationship_type_rec.direction_code,
p_old_column_value => l_direction_code,
x_return_status => x_return_status);
'direction_code is non-updateable. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'direction_code is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
p_create_update_flag => p_create_update_flag,
p_column => 'subject_type',
p_column_value => p_relationship_type_rec.subject_type,
x_return_status => x_return_status);
SELECT 'Y'
INTO l_dummy
FROM FND_OBJECT_INSTANCE_SETS
WHERE INSTANCE_SET_NAME = p_relationship_type_rec.subject_type;
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'subject_type',
p_column_value => p_relationship_type_rec.subject_type,
p_old_column_value => l_subject_type,
x_return_status => x_return_status);
'subject_type is non-updateable. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'subject_type is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
p_create_update_flag => p_create_update_flag,
p_column => 'object_type',
p_column_value => p_relationship_type_rec.object_type,
x_return_status => x_return_status);
SELECT 'Y'
INTO l_dummy
FROM FND_OBJECT_INSTANCE_SETS
WHERE INSTANCE_SET_NAME = p_relationship_type_rec.object_type;
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'object_type',
p_column_value => p_relationship_type_rec.object_type,
p_old_column_value => l_object_type,
x_return_status => x_return_status);
'object_type is non-updateable. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'object_type is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
| SELECT COUNT(*)
| INTO l_count
| FROM HZ_RELATIONSHIP_TYPES
| WHERE RELATIONSHIP_TYPE = p_relationship_type_rec.relationship_type
| AND FORWARD_REL_CODE = p_relationship_type_rec.forward_rel_code
| AND SUBJECT_TYPE = p_relationship_type_rec.subject_type
| AND OBJECT_TYPE = p_relationship_type_rec.object_type;
| SELECT BACKWARD_REL_CODE
| INTO l_code
| FROM HZ_RELATIONSHIP_TYPES
| WHERE RELATIONSHIP_TYPE = p_relationship_type_rec.relationship_type
| AND FORWARD_REL_CODE = p_relationship_type_rec.forward_rel_code
| AND SUBJECT_TYPE = p_relationship_type_rec.subject_type
| AND OBJECT_TYPE = p_relationship_type_rec.object_type
| AND ROWNUM = 1;
SELECT COUNT(*)
INTO l_count
FROM HZ_RELATIONSHIP_TYPES
WHERE
(
RELATIONSHIP_TYPE = p_relationship_type_rec.relationship_type
)
AND
(
(
(SUBJECT_TYPE = p_relationship_type_rec.subject_type
AND OBJECT_TYPE = p_relationship_type_rec.object_type
)
AND
(
(BACKWARD_REL_CODE <> p_relationship_type_rec.backward_rel_code
AND FORWARD_REL_CODE = p_relationship_type_rec.forward_rel_code
)
OR
(BACKWARD_REL_CODE = p_relationship_type_rec.backward_rel_code
AND FORWARD_REL_CODE <> p_relationship_type_rec.forward_rel_code
)
)
)
OR
(
(SUBJECT_TYPE = p_relationship_type_rec.object_type
AND OBJECT_TYPE = p_relationship_type_rec.subject_type
)
AND
(
(BACKWARD_REL_CODE <> p_relationship_type_rec.forward_rel_code
AND FORWARD_REL_CODE = p_relationship_type_rec.backward_rel_code
)
OR
(BACKWARD_REL_CODE = p_relationship_type_rec.forward_rel_code
AND FORWARD_REL_CODE <> p_relationship_type_rec.backward_rel_code
)
)
)
);
SELECT 1
INTO l_count
FROM HZ_RELATIONSHIP_TYPES
WHERE RELATIONSHIP_TYPE = p_relationship_type_rec.relationship_type
AND FORWARD_REL_CODE = p_relationship_type_rec.forward_rel_code
AND BACKWARD_REL_CODE = p_relationship_type_rec.backward_rel_code
AND SUBJECT_TYPE = p_relationship_type_rec.subject_type
AND OBJECT_TYPE = p_relationship_type_rec.object_type
AND RELATIONSHIP_TYPE_ID <> NVL(p_relationship_type_rec.relationship_type_id,-1);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'hierarchical_flag',
p_column_value => p_relationship_type_rec.hierarchical_flag,
p_old_column_value => l_hierarchical_flag,
x_return_status => x_return_status);
'hierarchical_flag is non-updateable. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'hierarchical_flag is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' THEN
-- if hierarchical_flag = 'Y', then allow_circular_relationships must be 'N'
IF NVL(p_relationship_type_rec.hierarchical_flag, 'N') = 'Y' THEN
IF p_relationship_type_rec.allow_circular_relationships = 'Y' THEN
fnd_message.set_name('AR', 'HZ_API_VAL_DEP_FIELDS');
IF p_create_update_flag = 'C' THEN
-- if hierarchical_flag = 'Y', the direction_code must be 'P' or 'C'
IF NVL(p_relationship_type_rec.hierarchical_flag, 'N') = 'Y' THEN
IF p_relationship_type_rec.direction_code = 'N' THEN
fnd_message.set_name('AR', 'HZ_API_VAL_DEP_FIELDS');
IF p_create_update_flag = 'C' THEN
BEGIN
SELECT count(*)
INTO l_count
FROM HZ_RELATIONSHIP_TYPES
WHERE ROLE = p_relationship_type_rec.forward_role
OR ROLE = p_relationship_type_rec.backward_role;
IF p_create_update_flag = 'C' THEN
IF ( p_relationship_type_rec.forward_role IS NOT NULL AND
p_relationship_type_rec.forward_role <> fnd_api.g_miss_char) AND
( p_relationship_type_rec.backward_role IS NOT NULL AND
p_relationship_type_rec.backward_role <> fnd_api.g_miss_char) THEN
validate_rel_code(
p_forward_rel_code =>p_relationship_type_rec.forward_rel_code,
p_backward_rel_code =>p_relationship_type_rec.backward_rel_code,
p_forward_role => p_relationship_type_rec.forward_role,
p_backward_role => p_relationship_type_rec.backward_role,
x_return_status => x_return_status);
IF p_create_update_flag = 'C' THEN
IF p_relationship_type_rec.forward_role IS NOT NULL THEN
validate_lookup (
p_column => 'role',
p_lookup_type => 'HZ_RELATIONSHIP_ROLE',
p_column_value => p_relationship_type_rec.forward_role,
x_return_status => x_return_status);
IF p_create_update_flag = 'C' THEN
-- if hierarchical_flag = 'Y', then allow_relate_to_self_flag must be 'N'
IF NVL(p_relationship_type_rec.hierarchical_flag, 'N') = 'Y' THEN
IF p_relationship_type_rec.allow_relate_to_self_flag = 'Y' THEN
fnd_message.set_name('AR', 'HZ_API_VAL_DEP_FIELDS');
IF p_create_update_flag = 'C' THEN
-- if direction_code = 'N', the allow_circular_relationships must be 'Y'
IF p_relationship_type_rec.direction_code = 'N' THEN
IF NVL(p_relationship_type_rec.allow_circular_relationships, 'Y') = 'N' THEN
fnd_message.set_name('AR', 'HZ_API_VAL_DEP_FIELDS');
IF p_create_update_flag = 'C' THEN
-- if hierarchical_flag = 'N', then multiple_parent_allowed must be 'Y'
IF NVL(p_relationship_type_rec.hierarchical_flag, 'N') = 'N' THEN
IF NVL(p_relationship_type_rec.multiple_parent_allowed, 'Y') = 'N' THEN
fnd_message.set_name('AR', 'HZ_API_VAL_DEP_FIELDS');
IF p_create_update_flag = 'C' THEN
BEGIN
SELECT HIERARCHICAL_FLAG
INTO l_hierarchical_flag
FROM HZ_RELATIONSHIP_TYPES
WHERE RELATIONSHIP_TYPE = p_relationship_type_rec.relationship_type
AND ROWNUM = 1;
IF p_create_update_flag = 'C' THEN
BEGIN
SELECT ALLOW_CIRCULAR_RELATIONSHIPS
INTO l_allow_circular_relationships
FROM HZ_RELATIONSHIP_TYPES
WHERE RELATIONSHIP_TYPE = p_relationship_type_rec.relationship_type
AND ROWNUM = 1;
IF p_create_update_flag = 'C' THEN
BEGIN
SELECT MULTIPLE_PARENT_ALLOWED
INTO l_multiple_parent_allowed
FROM HZ_RELATIONSHIP_TYPES
WHERE RELATIONSHIP_TYPE = p_relationship_type_rec.relationship_type
AND ROWNUM = 1;
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'multiple_parent_allowed',
p_column_value => p_relationship_type_rec.multiple_parent_allowed,
p_old_column_value => l_multiple_parent_allowed,
x_return_status => x_return_status);
'multiple_parent_allowed is non-updateable. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'multiple_parent_allowed is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_relationship_type_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_relationship_type_rec.application_id,
p_old_application_id => l_application_id,
x_return_status => x_return_status);
IF (p_create_update_flag = 'C') THEN
IF((p_relationship_type_rec.forward_rel_code = p_relationship_type_rec.backward_rel_code)
AND (p_relationship_type_rec.subject_type <> p_relationship_type_rec.object_type))
THEN
fnd_message.set_name('AR', 'HZ_API_RELTYPE_INVALID');
* non-updateable fields
* foreign key validations
* other validations
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_relationship_rec Relationship record.
* p_rowid Rowid of the record (used only in update mode).
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* fnd_api.g_ret_sts_error (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 07-23-2001 Indrajit Sen o Created.
* 13-DEC-2001 Joe del Callar Bug 2145637: Modified to use the new
* time-dependent date overlap check.
*/
PROCEDURE validate_relationship(
p_create_update_flag IN VARCHAR2,
p_relationship_rec IN hz_relationship_v2pub.relationship_rec_type,
p_rowid IN ROWID ,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_count NUMBER;
SELECT hr.start_date,
hr.end_date,
hr.relationship_id,
hr.status
FROM hz_relationships hr
WHERE hr.subject_id = l_subject_id
AND hr.object_id = l_object_id
AND hr.subject_table_name = l_subject_table_name
AND hr.subject_type = l_subject_type
AND hr.object_table_name = l_object_table_name
AND hr.object_type = l_object_type
AND hr.relationship_code = l_relationship_code
AND hr.relationship_type = l_relationship_type
--AND hr.directional_flag = 'F'
AND hr.status = 'A'
AND hr.actual_content_source = p_data_source
AND NVL(p_relationship_rec.relationship_id, fnd_api.g_miss_num) <> hr.relationship_id;
IF (p_create_update_flag = 'U') THEN
-- Bug 2197181: selecting actual_content_source for mix-n-match
SELECT subject_id,
subject_type,
subject_table_name,
object_id,
object_type,
object_table_name,
relationship_code,
relationship_type,
party_id,
content_source_type,
start_date,
end_date,
status,
created_by_module,
application_id,
actual_content_source
INTO l_subject_id,
l_subject_type,
l_subject_table_name,
l_object_id,
l_object_type,
l_object_table_name,
l_relationship_code,
l_relationship_type,
l_party_id,
l_content_source_type,
l_start_date,
l_end_date,
l_status,
l_created_by_module,
l_application_id,
db_actual_content_source
FROM hz_relationships
WHERE ROWID = p_rowid;
p_create_update_flag => p_create_update_flag,
p_column => 'relationship_type',
p_column_value => p_relationship_rec.relationship_type,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'relationship_type',
p_column_value => p_relationship_rec.relationship_type,
p_old_column_value => l_relationship_type,
x_return_status => x_return_status
);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'relationship_type is non-updateable. ' ||
'x_return_status = ' || x_return_status,
l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'relationship_type is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
p_create_update_flag => p_create_update_flag,
p_column => 'relationship_code',
p_column_value => p_relationship_rec.relationship_code,
x_return_status => x_return_status
);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'relationship_code',
p_column_value => p_relationship_rec.relationship_code,
p_old_column_value => l_relationship_code,
x_return_status => x_return_status
);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'relationship_code is non-updateable. ' ||
'x_return_status = ' || x_return_status,
l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'relationship_code is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' THEN
validate_lookup (
p_column => 'relationship_code',
p_lookup_type => 'PARTY_RELATIONS_TYPE',
p_column_value => p_relationship_rec.relationship_code,
x_return_status => x_return_status
);
p_create_update_flag => p_create_update_flag,
p_column => 'subject_id',
p_column_value => p_relationship_rec.subject_id,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'subject_id',
p_column_value => p_relationship_rec.subject_id,
p_old_column_value => l_subject_id,
x_return_status => x_return_status
);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'subject_id is non-updateable. ' ||
'x_return_status = ' || x_return_status,
l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'subject_id is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
AND p_create_update_flag = 'C'
THEN
l_in := hz_relationship_type_v2pub.in_instance_sets (
p_relationship_rec.subject_type,
p_relationship_rec.subject_id
);
p_create_update_flag => p_create_update_flag,
p_column => 'subject_table_name',
p_column_value => p_relationship_rec.subject_table_name,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'subject_table_name',
p_column_value => p_relationship_rec.subject_table_name,
p_old_column_value => l_subject_table_name,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'subject_table_name is non-updateable. ' ||
'x_return_status = ' || x_return_status,
l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'subject_table_name is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C'
AND p_relationship_rec.subject_table_name IS NOT NULL
AND p_relationship_rec.subject_table_name <> fnd_api.g_miss_char
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM fnd_objects fo
WHERE fo.obj_name = p_relationship_rec.subject_table_name;
p_create_update_flag => p_create_update_flag,
p_column => 'subject_type',
p_column_value => p_relationship_rec.subject_type,
x_return_status => x_return_status
);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'subject_type',
p_column_value => p_relationship_rec.subject_type,
p_old_column_value => l_subject_type,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'subject_type is non-updateable. ' ||
'x_return_status = ' || x_return_status,
l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'subject_type is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C'
AND p_relationship_rec.subject_type IS NOT NULL
AND p_relationship_rec.subject_type <> fnd_api.g_miss_char
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM fnd_object_instance_sets fois
WHERE fois.instance_set_name = p_relationship_rec.subject_type;
p_create_update_flag => p_create_update_flag,
p_column => 'object_id',
p_column_value => p_relationship_rec.object_id,
x_return_status => x_return_status
);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'object_id',
p_column_value => p_relationship_rec.object_id,
p_old_column_value => l_object_id,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'object_id is non-updateable. ' ||
'x_return_status = ' || x_return_status,
l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'object_id is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
AND p_create_update_flag = 'C'
THEN
l_in := hz_relationship_type_v2pub.in_instance_sets (
p_relationship_rec.object_type,
p_relationship_rec.object_id
);
p_create_update_flag => p_create_update_flag,
p_column => 'object_table_name',
p_column_value => p_relationship_rec.object_table_name,
x_return_status => x_return_status
);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'object_table_name',
p_column_value => p_relationship_rec.object_table_name,
p_old_column_value => l_object_table_name,
x_return_status => x_return_status
);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'object_table_name is non-updateable. ' ||
'x_return_status = ' || x_return_status,
l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'object_table_name is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C'
AND p_relationship_rec.object_table_name IS NOT NULL
AND p_relationship_rec.object_table_name <> fnd_api.g_miss_char
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM fnd_objects fo
WHERE fo.obj_name = p_relationship_rec.object_table_name;
p_create_update_flag => p_create_update_flag,
p_column => 'object_type',
p_column_value => p_relationship_rec.object_type,
x_return_status => x_return_status
);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'object_type',
p_column_value => p_relationship_rec.object_type,
p_old_column_value => l_object_type,
x_return_status => x_return_status
);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'object_type is non-updateable. ' ||
'x_return_status = ' || x_return_status,
l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'object_type is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C'
AND p_relationship_rec.object_type IS NOT NULL
AND p_relationship_rec.object_type <> fnd_api.g_miss_char
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM fnd_object_instance_sets fois
WHERE fois.instance_set_name = p_relationship_rec.object_type;
p_create_update_flag => p_create_update_flag,
p_column => 'start_date',
p_column_value => p_relationship_rec.start_date,
x_return_status => x_return_status
);
IF p_create_update_flag = 'U' THEN
validate_cannot_update_to_null (
p_column => 'start_date',
p_column_value => p_relationship_rec.start_date,
x_return_status => x_return_status
);
'start_date cannot be set to null during update. ' ||
'x_return_status = ' || x_return_status,
l_debug_prefix
);
p_message=>'start_date cannot be set to null during update. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'party_id',
p_column_value => p_relationship_rec.party_rec.party_id,
p_old_column_value => l_party_id,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,'party_id is non-updateable. ' ||
'x_return_status = ' || x_return_status,
l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'party_id is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'U' THEN
validate_cannot_update_to_null (
p_column => 'status',
p_column_value => p_relationship_rec.status,
x_return_status => x_return_status
);
'status cannot be set to null during update. ' ||
'x_return_status = ' || x_return_status,
l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'status cannot be set to null during update. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
AND (p_create_update_flag = 'C'
OR (p_create_update_flag = 'U'
AND p_relationship_rec.status <> NVL(l_status,
fnd_api.g_miss_char)))
THEN
validate_lookup (
p_column => 'status',
p_lookup_type => 'REGISTRY_STATUS',
p_column_value => p_relationship_rec.status,
x_return_status => x_return_status
);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'content_source_type',
p_column_value => p_relationship_rec.content_source_type,
p_old_column_value => l_content_source_type,
x_return_status => x_return_status
);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'content_source_type is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
p_create_update_flag => p_create_update_flag,
p_check_update_privilege => 'N',
p_content_source_type => p_relationship_rec.content_source_type,
p_old_content_source_type => l_content_source_type,
p_actual_content_source => p_relationship_rec.actual_content_source,
p_old_actual_content_source => db_actual_content_source,
p_entity_name => 'HZ_RELATIONSHIPS',
x_return_status => x_return_status );
IF (p_create_update_flag = 'C') THEN
IF p_relationship_rec.end_date IS NOT NULL AND
p_relationship_rec.end_date <> fnd_api.g_miss_date AND
p_relationship_rec.end_date < p_relationship_rec.start_date
THEN
fnd_message.set_name('AR', 'HZ_API_START_DATE_GREATER');
ELSIF (p_create_update_flag = 'U') THEN
-- old start_date, end_date has been selected from table
-- and put into l_start_date, l_end_date
IF p_relationship_rec.start_date <> fnd_api.g_miss_date THEN
l_start_date := p_relationship_rec.start_date;
IF p_create_update_flag = 'C' THEN
db_actual_content_source := p_relationship_rec.actual_content_source;
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_relationship_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_relationship_rec.application_id,
p_old_application_id => l_application_id,
x_return_status => x_return_status);
p_create_update_flag IN VARCHAR2,
p_contact_point_rec IN hz_contact_point_v2pub.contact_point_rec_type,
p_edi_rec IN hz_contact_point_v2pub.edi_rec_type := hz_contact_point_v2pub.g_miss_edi_rec,
p_email_rec IN hz_contact_point_v2pub.email_rec_type := hz_contact_point_v2pub.g_miss_email_rec,
p_phone_rec IN hz_contact_point_v2pub.phone_rec_type := hz_contact_point_v2pub.g_miss_phone_rec,
p_telex_rec IN hz_contact_point_v2pub.telex_rec_type := hz_contact_point_v2pub.g_miss_telex_rec,
p_web_rec IN hz_contact_point_v2pub.web_rec_type := hz_contact_point_v2pub.g_miss_web_rec,
p_rowid IN ROWID,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := '';
p_create_update_flag => p_create_update_flag,
p_contact_point_rec => p_contact_point_rec,
p_edi_rec => p_edi_rec,
p_email_rec => p_email_rec,
p_phone_rec => p_phone_rec,
p_telex_rec => p_telex_rec,
p_web_rec => p_web_rec,
p_rowid => p_rowid,
x_return_status => x_return_status
);
p_create_update_flag IN VARCHAR2,
p_contact_point_rec IN hz_contact_point_v2pub.contact_point_rec_type,
p_edi_rec IN hz_contact_point_v2pub.edi_rec_type := hz_contact_point_v2pub.g_miss_edi_rec,
p_rowid IN ROWID,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := '';
p_create_update_flag => p_create_update_flag,
p_contact_point_rec => p_contact_point_rec,
p_edi_rec => p_edi_rec,
p_rowid => p_rowid,
x_return_status => x_return_status
);
p_create_update_flag IN VARCHAR2,
p_contact_point_rec IN hz_contact_point_v2pub.contact_point_rec_type,
p_eft_rec IN hz_contact_point_v2pub.eft_rec_type := hz_contact_point_v2pub.g_miss_eft_rec,
p_rowid IN ROWID,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := '';
p_create_update_flag => p_create_update_flag,
p_contact_point_rec => p_contact_point_rec,
p_eft_rec => p_eft_rec,
p_rowid => p_rowid,
x_return_status => x_return_status
);
p_create_update_flag IN VARCHAR2,
p_contact_point_rec IN hz_contact_point_v2pub.contact_point_rec_type,
p_web_rec IN hz_contact_point_v2pub.web_rec_type := hz_contact_point_v2pub.g_miss_web_rec,
p_rowid IN ROWID,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := '';
p_create_update_flag => p_create_update_flag,
p_contact_point_rec => p_contact_point_rec,
p_web_rec => p_web_rec,
p_rowid => p_rowid,
x_return_status => x_return_status
);
p_create_update_flag IN VARCHAR2,
p_contact_point_rec IN hz_contact_point_v2pub.contact_point_rec_type,
p_phone_rec IN hz_contact_point_v2pub.phone_rec_type := hz_contact_point_v2pub.g_miss_phone_rec,
p_rowid IN ROWID,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := '';
p_create_update_flag => p_create_update_flag,
p_contact_point_rec => p_contact_point_rec,
p_phone_rec => p_phone_rec,
p_rowid => p_rowid,
x_return_status => x_return_status
);
p_create_update_flag IN VARCHAR2,
p_contact_point_rec IN hz_contact_point_v2pub.contact_point_rec_type,
p_telex_rec IN hz_contact_point_v2pub.telex_rec_type := hz_contact_point_v2pub.g_miss_telex_rec,
p_rowid IN ROWID,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := '';
p_create_update_flag => p_create_update_flag,
p_contact_point_rec => p_contact_point_rec,
p_telex_rec => p_telex_rec,
p_rowid => p_rowid,
x_return_status => x_return_status
);
p_create_update_flag IN VARCHAR2,
p_contact_point_rec IN hz_contact_point_v2pub.contact_point_rec_type,
p_email_rec IN hz_contact_point_v2pub.email_rec_type := hz_contact_point_v2pub.g_miss_email_rec,
p_rowid IN ROWID,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := '';
p_create_update_flag => p_create_update_flag,
p_contact_point_rec => p_contact_point_rec,
p_email_rec => p_email_rec,
p_rowid => p_rowid,
x_return_status => x_return_status
);
p_create_update_flag IN VARCHAR2,
p_organization_rec IN HZ_PARTY_V2PUB.ORGANIZATION_REC_TYPE,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_debug_prefix VARCHAR2(30) := '';
IF ( p_create_update_flag = 'C' AND
p_organization_rec.avg_high_credit IS NOT NULL AND
p_organization_rec.avg_high_credit <> FND_API.G_MISS_NUM )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.avg_high_credit = FND_API.G_MISS_NUM OR
p_organization_rec.avg_high_credit IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.credit_score IS NOT NULL AND
p_organization_rec.credit_score <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.credit_score = FND_API.G_MISS_CHAR OR
p_organization_rec.credit_score IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.credit_score_age IS NOT NULL AND
p_organization_rec.credit_score_age <> FND_API.G_MISS_NUM )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.credit_score_age = FND_API.G_MISS_NUM OR
p_organization_rec.credit_score_age IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.credit_score_class IS NOT NULL AND
p_organization_rec.credit_score_class <> FND_API.G_MISS_NUM )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.credit_score_class = FND_API.G_MISS_NUM OR
p_organization_rec.credit_score_class IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.credit_score_commentary IS NOT NULL AND
p_organization_rec.credit_score_commentary <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.credit_score_commentary = FND_API.G_MISS_CHAR OR
p_organization_rec.credit_score_commentary IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.credit_score_commentary2 IS NOT NULL AND
p_organization_rec.credit_score_commentary2 <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.credit_score_commentary2 = FND_API.G_MISS_CHAR OR
p_organization_rec.credit_score_commentary2 IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.credit_score_commentary3 IS NOT NULL AND
p_organization_rec.credit_score_commentary3 <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.credit_score_commentary3 = FND_API.G_MISS_CHAR OR
p_organization_rec.credit_score_commentary3 IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.credit_score_commentary4 IS NOT NULL AND
p_organization_rec.credit_score_commentary4 <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.credit_score_commentary4 = FND_API.G_MISS_CHAR OR
p_organization_rec.credit_score_commentary4 IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.credit_score_commentary5 IS NOT NULL AND
p_organization_rec.credit_score_commentary5 <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.credit_score_commentary5 = FND_API.G_MISS_CHAR OR
p_organization_rec.credit_score_commentary5 IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.credit_score_commentary6 IS NOT NULL AND
p_organization_rec.credit_score_commentary6 <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.credit_score_commentary6 = FND_API.G_MISS_CHAR OR
p_organization_rec.credit_score_commentary6 IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.credit_score_commentary7 IS NOT NULL AND
p_organization_rec.credit_score_commentary7 <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.credit_score_commentary7 = FND_API.G_MISS_CHAR OR
p_organization_rec.credit_score_commentary7 IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.credit_score_commentary8 IS NOT NULL AND
p_organization_rec.credit_score_commentary8 <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.credit_score_commentary8 = FND_API.G_MISS_CHAR OR
p_organization_rec.credit_score_commentary8 IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.credit_score_commentary9 IS NOT NULL AND
p_organization_rec.credit_score_commentary9 <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.credit_score_commentary9 = FND_API.G_MISS_CHAR OR
p_organization_rec.credit_score_commentary9 IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.credit_score_commentary10 IS NOT NULL AND
p_organization_rec.credit_score_commentary10 <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.credit_score_commentary10 = FND_API.G_MISS_CHAR OR
p_organization_rec.credit_score_commentary10 IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.credit_score_date IS NOT NULL AND
p_organization_rec.credit_score_date <> FND_API.G_MISS_DATE )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.credit_score_date = FND_API.G_MISS_DATE OR
p_organization_rec.credit_score_date IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.credit_score_incd_default IS NOT NULL AND
p_organization_rec.credit_score_incd_default <> FND_API.G_MISS_NUM )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.credit_score_incd_default = FND_API.G_MISS_NUM OR
p_organization_rec.credit_score_incd_default IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.credit_score_natl_percentile IS NOT NULL AND
p_organization_rec.credit_score_natl_percentile <> FND_API.G_MISS_NUM)
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.credit_score_natl_percentile = FND_API.G_MISS_NUM OR
p_organization_rec.credit_score_natl_percentile IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.db_rating IS NOT NULL AND
p_organization_rec.db_rating <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.db_rating = FND_API.G_MISS_CHAR OR
p_organization_rec.db_rating IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.debarment_ind IS NOT NULL AND
p_organization_rec.debarment_ind <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.debarment_ind = FND_API.G_MISS_CHAR OR
p_organization_rec.debarment_ind IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.debarments_count IS NOT NULL AND
p_organization_rec.debarments_count <> FND_API.G_MISS_NUM )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.debarments_count = FND_API.G_MISS_NUM OR
p_organization_rec.debarments_count IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.debarments_date IS NOT NULL AND
p_organization_rec.debarments_date <> FND_API.G_MISS_DATE )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.debarments_date = FND_API.G_MISS_DATE OR
p_organization_rec.debarments_date IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.high_credit IS NOT NULL AND
p_organization_rec.high_credit <> FND_API.G_MISS_NUM )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.high_credit = FND_API.G_MISS_NUM OR
p_organization_rec.high_credit IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.maximum_credit_currency_code IS NOT NULL AND
p_organization_rec.maximum_credit_currency_code <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.maximum_credit_currency_code = FND_API.G_MISS_CHAR OR
p_organization_rec.maximum_credit_currency_code IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.maximum_credit_recommendation IS NOT NULL AND
p_organization_rec.maximum_credit_recommendation <> FND_API.G_MISS_NUM)
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.maximum_credit_recommendation = FND_API.G_MISS_NUM OR
p_organization_rec.maximum_credit_recommendation IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.paydex_norm IS NOT NULL AND
p_organization_rec.paydex_norm <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.paydex_norm = FND_API.G_MISS_CHAR OR
p_organization_rec.paydex_norm IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.paydex_score IS NOT NULL AND
p_organization_rec.paydex_score <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.paydex_score = FND_API.G_MISS_CHAR OR
p_organization_rec.paydex_score IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.paydex_three_months_ago IS NOT NULL AND
p_organization_rec.paydex_three_months_ago <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.paydex_three_months_ago = FND_API.G_MISS_CHAR OR
p_organization_rec.paydex_three_months_ago IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.failure_score IS NOT NULL AND
p_organization_rec.failure_score <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.failure_score = FND_API.G_MISS_CHAR OR
p_organization_rec.failure_score IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.failure_score_age IS NOT NULL AND
p_organization_rec.failure_score_age <> FND_API.G_MISS_NUM )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.failure_score_age = FND_API.G_MISS_NUM OR
p_organization_rec.failure_score_age IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.failure_score_class IS NOT NULL AND
p_organization_rec.failure_score_class <> FND_API.G_MISS_NUM )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.failure_score_class = FND_API.G_MISS_NUM OR
p_organization_rec.failure_score_class IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.failure_score_commentary IS NOT NULL AND
p_organization_rec.failure_score_commentary <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.failure_score_commentary = FND_API.G_MISS_CHAR OR
p_organization_rec.failure_score_commentary IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.failure_score_commentary2 IS NOT NULL AND
p_organization_rec.failure_score_commentary2 <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.failure_score_commentary2 = FND_API.G_MISS_CHAR OR
p_organization_rec.failure_score_commentary2 IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.failure_score_commentary3 IS NOT NULL AND
p_organization_rec.failure_score_commentary3 <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.failure_score_commentary3 = FND_API.G_MISS_CHAR OR
p_organization_rec.failure_score_commentary3 IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.failure_score_commentary4 IS NOT NULL AND
p_organization_rec.failure_score_commentary4 <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.failure_score_commentary4 = FND_API.G_MISS_CHAR OR
p_organization_rec.failure_score_commentary4 IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.failure_score_commentary5 IS NOT NULL AND
p_organization_rec.failure_score_commentary5 <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.failure_score_commentary5 = FND_API.G_MISS_CHAR OR
p_organization_rec.failure_score_commentary5 IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.failure_score_commentary6 IS NOT NULL AND
p_organization_rec.failure_score_commentary6 <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.failure_score_commentary6 = FND_API.G_MISS_CHAR OR
p_organization_rec.failure_score_commentary6 IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.failure_score_commentary7 IS NOT NULL AND
p_organization_rec.failure_score_commentary7 <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.failure_score_commentary7 = FND_API.G_MISS_CHAR OR
p_organization_rec.failure_score_commentary7 IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.failure_score_commentary8 IS NOT NULL AND
p_organization_rec.failure_score_commentary8 <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.failure_score_commentary8 = FND_API.G_MISS_CHAR OR
p_organization_rec.failure_score_commentary8 IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.failure_score_commentary9 IS NOT NULL AND
p_organization_rec.failure_score_commentary9 <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.failure_score_commentary9 = FND_API.G_MISS_CHAR OR
p_organization_rec.failure_score_commentary9 IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.failure_score_commentary10 IS NOT NULL AND
p_organization_rec.failure_score_commentary10 <> FND_API.G_MISS_CHAR )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.failure_score_commentary10 = FND_API.G_MISS_CHAR OR
p_organization_rec.failure_score_commentary10 IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.failure_score_date IS NOT NULL AND
p_organization_rec.failure_score_date <> FND_API.G_MISS_DATE )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.failure_score_date = FND_API.G_MISS_DATE OR
p_organization_rec.failure_score_date IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.failure_score_incd_default IS NOT NULL AND
p_organization_rec.failure_score_incd_default <> FND_API.G_MISS_NUM )
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.failure_score_incd_default = FND_API.G_MISS_NUM OR
p_organization_rec.failure_score_incd_default IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.failure_score_natnl_percentile IS NOT NULL AND
p_organization_rec.failure_score_natnl_percentile <> FND_API.G_MISS_NUM)
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.failure_score_natnl_percentile = FND_API.G_MISS_NUM OR
p_organization_rec.failure_score_natnl_percentile IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.failure_score_override_code IS NOT NULL AND
p_organization_rec.failure_score_override_code <> FND_API.G_MISS_CHAR)
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.failure_score_override_code = FND_API.G_MISS_CHAR OR
p_organization_rec.failure_score_override_code IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
IF ( p_create_update_flag = 'C' AND
p_organization_rec.global_failure_score IS NOT NULL AND
p_organization_rec.global_failure_score <> FND_API.G_MISS_CHAR)
OR
( p_create_update_flag = 'U' AND
( p_organization_rec.global_failure_score = FND_API.G_MISS_CHAR OR
p_organization_rec.global_failure_score IS NOT NULL
) )
THEN
FND_MESSAGE.SET_NAME( 'AR', 'HZ_DNB_MOVED_COLUMN' );
* non-updateable fields
* foreign key validations
* other validations
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_financial_report_rec Financial report record.
* p_rowid Rowid of the record (used only in update mode).
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* fnd_api.g_ret_sts_error (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 29-JAN-2003 Sreedhar Mohan o Created.
* 11-MAR-2003 Sreedhar Mohan o Bug 2829046: Changed the message token values.
* 10-OCT-2003 Rajib Ranjan Borah o Bug Number 3148753.Commented old validation
* which did not take care of the case when both
* issue_period and report_start_date are not
* provided.Furthermore new validation does the check
* only if p_create_update_flag='C' as both these
* fields are non_updateable.
* 23-MAR-2004 Rajib Ranjan Borah o Bug 3456205.Validation for party_id being a foreing
* key of HZ_PARTIES and party_type should be
* 'ORGANIZATION', are performed using a single cursor now.
* o Validation on report_start_date, report_end_date,
* party_id will be performed only during creation as these
* are non-updateable columns.
* o Removed unused local variables.
* 01-APR-2004 Rajib Ranjan Borah o Bug 3539597.Commented out changes incorporated in fix
* 3200870. Issued_period and report_start_date cannot be
* both null or not null. Fix 3200870 had added
* date_report_issued to the list of attributes which cannot
* be NULL at the same time.
* o Modified cursor c_unique_financial_report_rec as it used
* fail earlier when issued_period was null.Truncated the
* date parameters.
* 01-03-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
* Actual_content_source will be validated against HZ_ORIG_SYSTEMS_B
* in HZ_MIXNM_UTILITY.ValidateContentSource instead of
* being validated against lookup OCNTENT_SOURCE_TYPE.
*/
PROCEDURE validate_financial_report(
p_create_update_flag IN VARCHAR2,
p_financial_report_rec IN HZ_ORGANIZATION_INFO_V2PUB.FINANCIAL_REPORT_REC_TYPE,
p_rowid IN ROWID,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
CURSOR c_dup_financial_report_id (p_financial_report_id IN NUMBER) IS
SELECT 'Y'
FROM hz_financial_reports hfr
WHERE hfr.financial_report_id = p_financial_report_id;
SELECT 'Y'
FROM hz_financial_reports
WHERE party_id = p_party_id
AND nvl(type_of_financial_report, 'XXX') = nvl(p_type_of_financial_report, 'XXX')
AND nvl(document_reference, 'XXX') = nvl(p_document_reference, 'XXX')
AND actual_content_source = nvl(p_actual_content_source,'USER_ENTERED')
AND trunc(nvl(date_report_issued, to_date('12/31/4712','MM/DD/YYYY'))) =
trunc(nvl(p_date_report_issued, to_date('12/31/4712','MM/DD/YYYY')))
AND nvl(issued_period, to_date('12/31/4712','MM/DD/YYYY')) =
nvl( p_issued_period, to_date('12/31/4712','MM/DD/YYYY'))
AND trunc(nvl(report_start_date, to_date('12/31/4712','MM/DD/YYYY'))) =
trunc(nvl(p_report_start_date, to_date('12/31/4712','MM/DD/YYYY')))
AND trunc(nvl(report_end_date, to_date('12/31/4712','MM/DD/YYYY'))) =
trunc(nvl(p_report_end_date, to_date('12/31/4712','MM/DD/YYYY')))
;
SELECT hp.party_type
FROM hz_parties hp
WHERE hp.party_id = p_financial_report_rec.party_id;
IF p_create_update_flag = 'U'
THEN
SELECT FINANCIAL_REPORT_ID,
PARTY_ID,
DOCUMENT_REFERENCE,
DATE_REPORT_ISSUED,
ISSUED_PERIOD,
-- REQUIRING_AUTHORITY,
TYPE_OF_FINANCIAL_REPORT,
REPORT_START_DATE,
REPORT_END_DATE,
-- AUDIT_IND,
-- CONSOLIDATED_IND,
-- ESTIMATED_IND,
-- FISCAL_IND,
-- FINAL_IND,
-- FORECAST_IND,
-- OPENING_IND,
-- PROFORMA_IND,
-- QUALIFIED_IND,
-- RESTATED_IND,
-- SIGNED_BY_PRINCIPALS_IND,
-- TRIAL_BALANCE_IND,
-- UNBALANCED_IND,
CONTENT_SOURCE_TYPE,
ACTUAL_CONTENT_SOURCE,
-- REQUEST_ID,
STATUS,
CREATED_BY_MODULE
INTO l_financial_report_id,
l_party_id,
l_document_reference,
l_date_report_issued,
l_issued_period,
-- l_requiring_authority,
l_type_of_financial_report,
l_report_start_date,
l_report_end_date,
-- l_audit_ind,
-- l_consolidated_ind,
-- l_estimated_ind,
-- l_fiscal_ind,
-- l_final_ind,
-- l_forecast_ind,
-- l_opening_ind,
-- l_proforma_ind,
-- l_qualified_ind,
-- l_restated_ind,
-- l_signed_by_principals_ind,
-- l_trial_balance_ind,
-- l_unbalanced_ind,
l_content_source_type,
l_actual_content_source,
-- l_request_id,
l_status,
l_created_by_module
FROM HZ_FINANCIAL_REPORTS
WHERE ROWID = p_rowid;
IF p_create_update_flag = 'C' THEN
IF p_financial_report_rec.financial_report_id IS NOT NULL AND
p_financial_report_rec.financial_report_id <> fnd_api.g_miss_num
THEN
OPEN c_dup_financial_report_id (p_financial_report_rec.financial_report_id);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'financial_report_id',
p_column_value => p_financial_report_rec.financial_report_id,
p_old_column_value => l_financial_report_id,
x_return_status => x_return_status);
'financial_report_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'financial_report_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'party_id',
p_column_value => p_financial_report_rec.party_id,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'party_id',
p_column_value => p_financial_report_rec.party_id,
p_old_column_value => l_party_id,
x_return_status => x_return_status);
'party_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'party_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C'
AND
p_financial_report_rec.party_id IS NOT NULL
AND
p_financial_report_rec.party_id <> fnd_api.g_miss_num
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM hz_parties p
WHERE p.party_id = p_financial_report_rec.party_id;
IF p_create_update_flag = 'C'
/* Bug 3456205 Party_id is not updateable.
OR
p_create_update_flag = 'U'
If the value is NULL / G_MISS, error should be thrown in validate_mandatory only.
*/
AND p_financial_report_rec.party_id IS NOT NULL
AND p_financial_report_rec.party_id <> FND_API.G_MISS_NUM
THEN
--Bug 2886268: Added the following code instead of calling check_organization
OPEN c_partytype;
IF(p_create_update_flag='C')
THEN
IF (
(--Both the values are not set during creation.
(
p_financial_report_rec.issued_period IS NULL
OR
p_financial_report_rec.issued_period = fnd_api.g_miss_char
)
AND
(
p_financial_report_rec.report_start_date IS NULL
OR
p_financial_report_rec.report_start_date = fnd_api.g_miss_date
)
/* Bug 3539597.Commented out changes incorporated in fix 3200870.
| AND
| (
| p_financial_report_rec.DATE_REPORT_ISSUED IS NULL
| OR
| p_financial_report_rec.DATE_REPORT_ISSUED = fnd_api.g_miss_date
| )
*/
)
OR
(--Both the values are provided during creation
(
p_financial_report_rec.issued_period IS NOT NULL
AND
p_financial_report_rec.issued_period <> fnd_api.g_miss_char
)
AND
(
p_financial_report_rec.report_start_date IS NOT NULL
AND
p_financial_report_rec.report_start_date <> fnd_api.g_miss_date
)
)
)
THEN
fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION2');
IF p_create_update_flag = 'C' /*Bug 3456205 Both these columns are non-updateable */
AND (
(p_financial_report_rec.report_start_date IS NOT NULL
AND p_financial_report_rec.report_start_date <> fnd_api.g_miss_date
AND (p_financial_report_rec.report_end_date IS NULL OR
p_financial_report_rec.report_end_date = fnd_api.g_miss_date )
) OR
(p_financial_report_rec.report_end_date IS NOT NULL
AND p_financial_report_rec.report_end_date <> fnd_api.g_miss_date
AND (p_financial_report_rec.report_start_date IS NULL OR
p_financial_report_rec.report_start_date = fnd_api.g_miss_date)
))
THEN
fnd_message.set_name('AR', 'HZ_API_INVALID_COMBINATION3');
IF p_create_update_flag = 'C' THEN
IF p_financial_report_rec.report_end_date is NOT NULL AND
p_financial_report_rec.report_end_date <> FND_API.G_MISS_DATE THEN
if (p_financial_report_rec.report_end_date
< p_financial_report_rec.report_start_date
) THEN
FND_MESSAGE.SET_NAME('AR', 'HZ_API_START_DATE_GREATER');
/* Bug 3456205. As both report_start_date, report_end_date are non-updateable,
therefore this validation need not be performed during updation.
-- compare end_date with database data and user passed data.
ELSIF p_create_update_flag = 'U' THEN
if (p_financial_report_rec.report_end_date is NOT NULL AND
p_financial_report_rec.report_end_date <> FND_API.G_MISS_DATE) THEN
if p_financial_report_rec.report_start_date is NOT NULL AND
p_financial_report_rec.report_start_date <> FND_API.G_MISS_DATE then
if p_financial_report_rec.report_end_date
< p_financial_report_rec.report_start_date then
FND_MESSAGE.SET_NAME('AR', 'HZ_API_START_DATE_GREATER');
IF p_create_update_flag = 'C' THEN
OPEN c_unique_financial_report_rec ( p_financial_report_rec.party_id,
p_financial_report_rec.type_of_financial_report,
p_financial_report_rec.document_reference,
p_financial_report_rec.date_report_issued,
p_financial_report_rec.issued_period,
p_financial_report_rec.report_start_date,
p_financial_report_rec.report_end_date,
p_financial_report_rec.actual_content_source);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'type_of_financial_report',
p_column_value => p_financial_report_rec.type_of_financial_report,
p_old_column_value => l_type_of_financial_report,
x_return_status => x_return_status);
'type_of_financial_report is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'type_of_financial_report is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'document_reference',
p_column_value => p_financial_report_rec.document_reference,
p_old_column_value => l_document_reference,
x_return_status => x_return_status);
'document_reference is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'document_reference is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'date_report_issued',
p_column_value => trunc(p_financial_report_rec.date_report_issued),
p_old_column_value => trunc(l_date_report_issued),
x_return_status => x_return_status);
'date_report_issued is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'date_report_issued is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'issued_period',
p_column_value => p_financial_report_rec.issued_period,
p_old_column_value => l_issued_period,
x_return_status => x_return_status);
'issued_period is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'issued_period is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'report_start_date',
p_column_value => trunc(p_financial_report_rec.report_start_date),
p_old_column_value => trunc(l_report_start_date),
x_return_status => x_return_status);
'report_start_date is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'report_start_date is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'report_end_date',
p_column_value => trunc(p_financial_report_rec.report_end_date),
p_old_column_value => trunc(l_report_end_date),
x_return_status => x_return_status);
'report_end_date is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'report_end_date is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_financial_report_rec.status <> NVL(l_status, fnd_api.g_miss_char)
)
)
THEN
validate_lookup (
p_column => 'status',
p_lookup_type => 'REGISTRY_STATUS',
p_column_value => p_financial_report_rec.status,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_cannot_update_to_null (
p_column => 'status',
p_column_value => p_financial_report_rec.status,
x_return_status => x_return_status);
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'actual_content_source',
p_column_value => p_financial_report_rec.actual_content_source,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_check_update_privilege => 'Y',
p_content_source_type => 'USER_ENTERED',
p_old_content_source_type => 'USER_ENTERED',
p_actual_content_source => p_financial_report_rec.actual_content_source,
p_old_actual_content_source => l_actual_content_source,
p_entity_name => 'HZ_FINANCIAL_REPORTS',
x_return_status => x_return_status );
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_financial_report_rec.created_by_module,
p_old_created_by_module => l_created_by_module,
x_return_status => x_return_status);
* non-updateable fields
* foreign key validations
* other validations
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_financial_number_rec Financial number record.
* p_rowid Rowid of the record (used only in update mode).
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* fnd_api.g_ret_sts_error (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 29-JAN-2003 Sreedhar Mohan o Created.
*
*/
--bug 3942332: added out parameter x_actual_content_source
PROCEDURE validate_financial_number(
p_create_update_flag IN VARCHAR2,
p_financial_number_rec IN HZ_ORGANIZATION_INFO_V2PUB.FINANCIAL_NUMBER_REC_TYPE,
p_rowid IN ROWID,
x_return_status IN OUT NOCOPY VARCHAR2,
x_actual_content_source OUT NOCOPY VARCHAR2
) IS
CURSOR c_dup_financial_number_id (p_financial_report_id IN NUMBER) IS
SELECT 'Y'
FROM hz_financial_numbers hfr
WHERE hfr.financial_number_id = financial_number_id;
SELECT 'Y'
FROM hz_financial_numbers
WHERE financial_report_id = p_financial_report_id
AND nvl(financial_number_name, 'XXX') = nvl(p_financial_number_name, 'XXX');
IF p_create_update_flag = 'U'
THEN
SELECT FINANCIAL_NUMBER_ID,
FINANCIAL_REPORT_ID,
FINANCIAL_NUMBER_NAME,
STATUS,
CREATED_BY_MODULE
INTO l_financial_number_id,
l_financial_report_id,
l_financial_number_name,
l_status,
l_created_by_module
FROM HZ_FINANCIAL_NUMBERS
WHERE ROWID = p_rowid;
IF p_create_update_flag = 'C' THEN
IF p_financial_number_rec.financial_number_id IS NOT NULL AND
p_financial_number_rec.financial_number_id <> fnd_api.g_miss_num
THEN
OPEN c_dup_financial_number_id (p_financial_number_rec.financial_number_id);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'financial_number_id',
p_column_value => p_financial_number_rec.financial_number_id,
p_old_column_value => l_financial_number_id,
x_return_status => x_return_status);
'financial_number_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'financial_number_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C'
AND
p_financial_number_rec.financial_report_id IS NOT NULL
AND
p_financial_number_rec.financial_report_id <> fnd_api.g_miss_num
THEN
BEGIN
--bug 3942332: selected actual_content_source from the hz_financial_reports record.
--SELECT 'Y'
--INTO l_dummy
SELECT actual_content_source
INTO x_actual_content_source
FROM hz_financial_reports hfr
WHERE hfr.financial_report_id = p_financial_number_rec.financial_report_id;
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'financial_report_id',
p_column_value => p_financial_number_rec.financial_report_id,
p_old_column_value => l_financial_report_id,
x_return_status => x_return_status);
'financial_report_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'financial_report_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'financial_report_id',
p_column_value => p_financial_number_rec.financial_report_id,
x_return_status => x_return_status);
IF p_create_update_flag = 'C' THEN
OPEN c_unique_financial_number_rec ( p_financial_number_rec.financial_report_id,
p_financial_number_rec.financial_number_name);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'financial_number_name',
p_column_value => p_financial_number_rec.financial_number_name,
p_old_column_value => l_financial_number_name,
x_return_status => x_return_status);
'financial_number_name is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
p_message=>'financial_number_name is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_financial_number_rec.status <> NVL(l_status, fnd_api.g_miss_char)
)
)
THEN
validate_lookup (
p_column => 'status',
p_lookup_type => 'REGISTRY_STATUS',
p_column_value => p_financial_number_rec.status,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_cannot_update_to_null (
p_column => 'status',
p_column_value => p_financial_number_rec.status,
x_return_status => x_return_status);
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_financial_number_rec.created_by_module,
p_old_created_by_module => l_created_by_module,
x_return_status => x_return_status);
* non-updateable fields
* foreign key validations
* other validations
*
* EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
*
* ARGUMENTS
* IN:
* p_create_update_flag Create update flag. 'C' = create. 'U' = update.
* p_credit_rating_rec Credit rating record.
* p_rowid Rowid of the record (used only in update mode).
* IN/OUT:
* x_return_status Return status after the call. The status can
* be FND_API.G_RET_STS_SUCCESS (success),
* fnd_api.g_ret_sts_error (error),
* FND_API.G_RET_STS_UNEXP_ERROR (unexpected error).
*
* NOTES
*
* MODIFICATION HISTORY
*
* 01-27-2003 Sreedhar Mohan o Created.
* 03-11-2003 Sreedhar Mohan Changed the lookup_type to FAILURE_SCORE_OVERRIDE_CODE
* instead of FAILURE_SCORE_OVERRIDE_SCORE
* for the code credit_score_override_code
* 03-14-2003 Sreedhar Mohan Bug 2843453, modified such that, fincl_embt_ind should
* be validated against YES/NO instead of REGISTRY_STATUS
* 10-09-2003 Rajib R Borah o Bug 3090928.Commented the code to check for uniqueness of
* party_id,rated_as_of_date,rating_organization. This is now
* handled by the unique index HZ_CREDIT_RATINGS_U2.
* 04-05-2004 Rajib R Borah o Bug 3181460. Credit_score_natl_percentile and
* failure_score_natnl_percentile should have a value
* within 0 to 100 only.
* o Added local variables l_credit_score_natl_percentile and
* l_fail_score_natnl_percentile for the same.
* 01-03-2005 Rajib Ranjan Borah o SSM SST Integration and Extension.
* Actual_content_source will now be validated against
* HZ_ORIG_SYSTEMS_B by calling Hz_MIXNM_UTILITY.ValidateContentSource
* instead of being validated against lookup CONTENT_SOURCE_TYPE.
* 01-24-2005 Kalyan o Bug 3877782. Added the condition to consider 'start_date_active' and
* 'end_date_active'.
*/
PROCEDURE validate_credit_rating(
p_create_update_flag IN VARCHAR2,
p_credit_rating_rec IN HZ_PARTY_INFO_V2PUB.CREDIT_RATING_REC_TYPE,
p_rowid IN ROWID,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
CURSOR c_dup_credit_rating_id (p_credting_rating_id IN NUMBER) IS
SELECT 'Y'
FROM hz_credit_ratings hcr
WHERE hcr.credit_rating_id = p_credting_rating_id;
SELECT 'Y'
FROM hz_credit_ratings hc
WHERE hc.party_id = p_party_id
AND trunc(nvl(hc.rated_as_of_date, to_date('12/31/4712','MM/DD/YYYY'))) =
trunc(nvl(p_rated_as_of_date, to_date('12/31/4712','MM/DD/YYYY')))
AND nvl(hc.rating_organization, 'XXX') = nvl(p_rating_organization, 'XXX')
AND nvl(hc.actual_content_source, hz_party_v2pub.G_MISS_CONTENT_SOURCE_TYPE) =
nvl(p_actual_content_source, hz_party_v2pub.G_MISS_CONTENT_SOURCE_TYPE);
IF p_create_update_flag = 'U'
THEN
SELECT CREDIT_RATING_ID,
PARTY_ID,
RATING_ORGANIZATION,
RATED_AS_OF_DATE,
FINCL_EMBT_IND,
CREDIT_SCORE_COMMENTARY,
CREDIT_SCORE_COMMENTARY2,
CREDIT_SCORE_COMMENTARY3,
CREDIT_SCORE_COMMENTARY4,
CREDIT_SCORE_COMMENTARY5,
CREDIT_SCORE_COMMENTARY6,
CREDIT_SCORE_COMMENTARY7,
CREDIT_SCORE_COMMENTARY8,
CREDIT_SCORE_COMMENTARY9,
CREDIT_SCORE_COMMENTARY10,
FAILURE_SCORE_COMMENTARY,
FAILURE_SCORE_COMMENTARY2,
FAILURE_SCORE_COMMENTARY3,
FAILURE_SCORE_COMMENTARY4,
FAILURE_SCORE_COMMENTARY5,
FAILURE_SCORE_COMMENTARY6,
FAILURE_SCORE_COMMENTARY7,
FAILURE_SCORE_COMMENTARY8,
FAILURE_SCORE_COMMENTARY9,
FAILURE_SCORE_COMMENTARY10,
DEBARMENT_IND,
MAXIMUM_CREDIT_CURRENCY_CODE,
CREDIT_SCORE_OVERRIDE_CODE,
SUIT_IND,
LIEN_IND,
JUDGEMENT_IND,
BANKRUPTCY_IND,
NO_TRADE_IND,
PRNT_HQ_BKCY_IND,
ACTUAL_CONTENT_SOURCE,
STATUS,
CREATED_BY_MODULE,
CREDIT_SCORE_NATL_PERCENTILE,
FAILURE_SCORE_NATNL_PERCENTILE
INTO l_credit_rating_id,
l_party_id,
l_rating_organization,
l_rated_as_of_date,
l_fincl_embt_ind,
l_credit_score_commentary,
l_credit_score_commentary2,
l_credit_score_commentary3,
l_credit_score_commentary4,
l_credit_score_commentary5,
l_credit_score_commentary6,
l_credit_score_commentary7,
l_credit_score_commentary8,
l_credit_score_commentary9,
l_credit_score_commentary10,
l_failure_score_commentary,
l_failure_score_commentary2,
l_failure_score_commentary3,
l_failure_score_commentary4,
l_failure_score_commentary5,
l_failure_score_commentary6,
l_failure_score_commentary7,
l_failure_score_commentary8,
l_failure_score_commentary9,
l_failure_score_commentary10,
l_debarment_ind,
l_maximum_credit_currency_code,
l_credit_score_override_code,
l_suit_ind,
l_lien_ind,
l_judgement_ind,
l_bankruptcy_ind,
l_no_trade_ind,
l_prnt_hq_bkcy_ind,
l_actual_content_source,
l_status,
l_created_by_module,
l_credit_score_natl_percentile,
l_fail_score_natnl_percentile
FROM HZ_CREDIT_RATINGS
WHERE ROWID = p_rowid;
IF p_create_update_flag = 'C' THEN
IF p_credit_rating_rec.credit_rating_id IS NOT NULL AND
p_credit_rating_rec.credit_rating_id <> fnd_api.g_miss_num
THEN
OPEN c_dup_credit_rating_id (p_credit_rating_rec.credit_rating_id);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'credit_rating_id',
p_column_value => p_credit_rating_rec.credit_rating_id,
p_old_column_value => l_credit_rating_id,
x_return_status => x_return_status);
'credit_rating_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'credit_rating_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'party_id',
p_column_value => p_credit_rating_rec.party_id,
x_return_status => x_return_status);
IF p_create_update_flag = 'C'
AND
p_credit_rating_rec.party_id IS NOT NULL
AND
p_credit_rating_rec.party_id <> fnd_api.g_miss_num
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM hz_parties p
WHERE p.party_id = p_credit_rating_rec.party_id;
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'party_id',
p_column_value => p_credit_rating_rec.party_id,
p_old_column_value => l_party_id,
x_return_status => x_return_status);
'party_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'party_id is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
HZ_CREDIT_RATINGS_PKG.Insert_Row.
***********************************************************************************
|---------------------------------------
|--Bug 2869178: Unique record Validation
|---------------------------------------
|IF p_create_update_flag = 'C' THEN
| OPEN c_unique_credit_rating_rec (p_credit_rating_rec.party_id,
| p_credit_rating_rec.rated_as_of_date,
| p_credit_rating_rec.rating_organization,
| p_credit_rating_rec.actual_content_source);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'rated_as_of_date',
p_column_value => trunc(p_credit_rating_rec.rated_as_of_date),
p_old_column_value => trunc(l_rated_as_of_date),
x_return_status => x_return_status);
'rated_as_of_date is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'rated_as_of_date is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'U' THEN
validate_nonupdateable (
p_column => 'rating_organization',
p_column_value => p_credit_rating_rec.rating_organization,
p_old_column_value => l_rating_organization,
x_return_status => x_return_status);
'rating_organization is non-updateable field. ' ||
'x_return_status = ' || x_return_status, l_debug_prefix);
hz_utility_v2pub.debug(p_prefix=>l_debug_prefix,p_message=>'rating_organization is non-updateable field. ' ||
'x_return_status = ' || x_return_status,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C'
AND
p_credit_rating_rec.maximum_credit_currency_code IS NOT NULL
AND
p_credit_rating_rec.maximum_credit_currency_code <> fnd_api.g_miss_char
THEN
BEGIN
--Bug 2898670: Changed the validation as the foreign key validation
--has to be against active records
--Bug 3877782. Added the condition to consider start_date_active and end_date_active.
SELECT 'Y'
INTO l_dummy
FROM fnd_currencies f
WHERE f.currency_code = p_credit_rating_rec.maximum_credit_currency_code
AND currency_flag = 'Y'
AND enabled_flag = 'Y'
AND trunc(sysdate) between trunc(nvl(start_date_active,sysdate))
and trunc(nvl(end_date_active,sysdate));
(p_create_update_flag = 'C'
OR
(p_create_update_flag = 'U'
AND
p_credit_rating_rec.status <> NVL(l_status, fnd_api.g_miss_char)
)
)
THEN
validate_lookup (
p_column => 'status',
p_lookup_type => 'REGISTRY_STATUS',
p_column_value => p_credit_rating_rec.status,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
validate_cannot_update_to_null (
p_column => 'status',
p_column_value => p_credit_rating_rec.status,
x_return_status => x_return_status);
(p_create_update_flag = 'C' OR
(
p_create_update_flag = 'U' AND
p_credit_rating_rec.credit_score_natl_percentile <> l_credit_score_natl_percentile
)
)
THEN
IF p_credit_rating_rec.credit_score_natl_percentile < 0 OR
p_credit_rating_rec.credit_score_natl_percentile > 100
THEN
FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_PERCENTAGE');
p_create_update_flag = 'C' OR
(
p_create_update_flag = 'U' AND
p_credit_rating_rec.failure_score_natnl_percentile <> l_fail_score_natnl_percentile
)
)
THEN
IF p_credit_rating_rec.failure_score_natnl_percentile < 0 OR
p_credit_rating_rec.failure_score_natnl_percentile > 100
THEN
FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_PERCENTAGE');
p_create_update_flag => p_create_update_flag,
p_created_by_module => p_credit_rating_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_check_update_privilege => 'Y',
p_content_source_type => 'USER_ENTERED',
p_old_content_source_type => 'USER_ENTERED',
p_actual_content_source => p_credit_rating_rec.actual_content_source,
p_old_actual_content_source => l_actual_content_source,
p_entity_name => 'HZ_CREDIT_RATINGS',
x_return_status => x_return_status );
* p_create_update_flag create update flag
* p_created_by_module created by module
* p_old_created_by_module old value of created by module
* x_return_status return status
*/
PROCEDURE validate_created_by_module (
p_create_update_flag IN VARCHAR2,
p_created_by_module IN VARCHAR2,
p_old_created_by_module IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_column CONSTANT VARCHAR2(30) := 'created_by_module';
IF p_create_update_flag = 'C' THEN
validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => l_column,
p_column_value => p_created_by_module,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' AND
p_created_by_module IS NOT NULL
THEN
validate_nonupdateable (
p_column => l_column,
p_column_value => p_created_by_module,
p_old_column_value => p_old_created_by_module,
p_restricted => 'N',
x_return_status => x_return_status);
p_message => l_column || ' is non-updateable. It can be updated from NULL to a value. ' ||
'x_return_status = ' || x_return_status,
p_msg_level => fnd_log.level_statement);
(p_create_update_flag = 'C' OR
(p_create_update_flag = 'U' AND
(p_old_created_by_module IS NULL OR
p_created_by_module <> p_old_created_by_module)))
THEN
validate_lookup (
p_column => l_column,
p_lookup_type => 'HZ_CREATED_BY_MODULES',
p_column_value => p_created_by_module,
x_return_status => x_return_status);
* p_create_update_flag create update flag
* p_application_id application id
* p_old_application_id old value of application id
* x_return_status return status
*/
PROCEDURE validate_application_id (
p_create_update_flag IN VARCHAR2,
p_application_id IN NUMBER,
p_old_application_id IN NUMBER,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_column CONSTANT VARCHAR2(30) := 'application_id';
IF p_create_update_flag = 'U' AND
p_application_id IS NOT NULL
THEN
validate_nonupdateable (
p_column => l_column,
p_column_value => p_application_id,
p_old_column_value => p_old_application_id,
p_restricted => 'N',
x_return_status => x_return_status);
p_message => l_column || ' is non-updateable. It can be updated from NULL to a value. ' ||
'x_return_status = ' || x_return_status,
p_msg_level => fnd_log.level_statement);