[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE get_updated_record (
p_style_fmt_locale_id IN NUMBER,
p_update_field_rec IN HZ_STYLE_FMT_LOCALE_V2PUB.STYLE_FMT_LOCALE_REC_TYPE,
x_updated_rec OUT NOCOPY HZ_STYLE_FMT_LOCALE_V2PUB.STYLE_FMT_LOCALE_REC_TYPE
) IS
BEGIN
SELECT
style_fmt_locale_id,
style_format_code,
language_code,
territory_code,
DECODE ( p_update_field_rec.start_date_active, null, start_date_active, fnd_api.g_miss_date, null, p_update_field_rec.start_date_active),
DECODE ( p_update_field_rec.end_date_active, null, end_date_active, fnd_api.g_miss_date, null, p_update_field_rec.end_date_active)
INTO
x_updated_rec.style_fmt_locale_id,
x_updated_rec.style_format_code,
x_updated_rec.language_code,
x_updated_rec.territory_code,
x_updated_rec.start_date_active,
x_updated_rec.end_date_active
FROM HZ_STYLE_FMT_LOCALES
WHERE style_fmt_locale_id = p_style_fmt_locale_id;
END get_updated_record;
PROCEDURE get_updated_record (
p_style_fmt_layout_id IN NUMBER,
p_update_field_rec IN HZ_STYLE_FMT_LAYOUT_V2PUB.STYLE_FMT_LAYOUT_REC_TYPE,
x_updated_rec OUT NOCOPY HZ_STYLE_FMT_LAYOUT_V2PUB.STYLE_FMT_LAYOUT_REC_TYPE
) IS
BEGIN
SELECT
b.style_fmt_layout_id,
b.style_format_code,
b.variation_number,
b.attribute_code,
b.attribute_application_id,
DECODE ( p_update_field_rec.line_number, null, b.line_number, fnd_api.g_miss_num, null, p_update_field_rec.line_number),
DECODE ( p_update_field_rec.position, null, b.position, fnd_api.g_miss_num, null, p_update_field_rec.position),
DECODE ( p_update_field_rec.mandatory_flag, null, b.mandatory_flag, fnd_api.g_miss_char, null, p_update_field_rec.mandatory_flag),
DECODE ( p_update_field_rec.use_initial_flag, null, b.use_initial_flag, fnd_api.g_miss_char, null, p_update_field_rec.use_initial_flag),
DECODE ( p_update_field_rec.uppercase_flag, null, b.uppercase_flag, fnd_api.g_miss_char, null, p_update_field_rec.uppercase_flag),
DECODE ( p_update_field_rec.transform_function, null, b.transform_function, fnd_api.g_miss_char, null, p_update_field_rec.transform_function),
DECODE ( p_update_field_rec.delimiter_before, null, b.delimiter_before, fnd_api.g_miss_char, null, p_update_field_rec.delimiter_before),
DECODE ( p_update_field_rec.delimiter_after, null, b.delimiter_after, fnd_api.g_miss_char, null, p_update_field_rec.delimiter_after),
DECODE ( p_update_field_rec.blank_lines_before, null, b.blank_lines_before, fnd_api.g_miss_num, null, p_update_field_rec.blank_lines_before),
DECODE ( p_update_field_rec.blank_lines_after, null, b.blank_lines_after, fnd_api.g_miss_num, null, p_update_field_rec.blank_lines_after),
DECODE ( p_update_field_rec.prompt, null, t.prompt, fnd_api.g_miss_char, null, p_update_field_rec.prompt),
DECODE ( p_update_field_rec.start_date_active, null, b.start_date_active, fnd_api.g_miss_date, null, p_update_field_rec.start_date_active),
DECODE ( p_update_field_rec.end_date_active, null, b.end_date_active, fnd_api.g_miss_date, null, p_update_field_rec.end_date_active)
INTO
x_updated_rec.style_fmt_layout_id,
x_updated_rec.style_format_code,
x_updated_rec.variation_number,
x_updated_rec.attribute_code,
x_updated_rec.attribute_application_id,
x_updated_rec.line_number,
x_updated_rec.position,
x_updated_rec.mandatory_flag,
x_updated_rec.use_initial_flag,
x_updated_rec.uppercase_flag,
x_updated_rec.transform_function,
x_updated_rec.delimiter_before,
x_updated_rec.delimiter_after,
x_updated_rec.blank_lines_before,
x_updated_rec.blank_lines_after,
x_updated_rec.prompt,
x_updated_rec.start_date_active,
x_updated_rec.end_date_active
FROM HZ_STYLE_FMT_LAYOUTS_B b , HZ_STYLE_FMT_LAYOUTS_TL t
WHERE b.style_fmt_layout_id =t.style_fmt_layout_id AND
t.style_fmt_layout_id = p_style_fmt_layout_id AND
t.language=userenv('LANG'); ---Bug No. 5178007
END get_updated_record;
p_create_update_flag IN VARCHAR2,
p_style_rec IN HZ_STYLE_V2PUB.STYLE_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 b.style_code, t.style_name,
b.database_object_name
INTO l_style_code,
l_style_name,
l_database_object_name
FROM HZ_STYLES_B b , HZ_STYLES_TL t
WHERE b.ROWID = p_rowid
AND t.style_code = b.style_code
AND t.language = userenv('LANG');
IF (p_create_update_flag = 'C') THEN
HZ_UTILITY_V2PUB.validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'style_code',
p_column_value => p_style_rec.style_code,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' AND
p_style_rec.style_code IS NOT NULL
THEN
HZ_UTILITY_V2PUB.validate_nonupdateable (
p_column => 'style_code',
p_column_value => p_style_rec.style_code,
p_old_column_value => l_style_code,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_message=>'style_code is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' AND
p_style_rec.style_code IS NOT NULL
THEN
BEGIN
select 'Y' into l_dummy
from HZ_STYLES_B
where style_code = p_style_rec.style_code;
IF (p_create_update_flag = 'C') THEN
HZ_UTILITY_V2PUB.validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'database_object_name',
p_column_value => p_style_rec.database_object_name,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' AND
p_style_rec.database_object_name IS NOT NULL
THEN
HZ_UTILITY_V2PUB.validate_nonupdateable (
p_column => 'database_object_name',
p_column_value => p_style_rec.database_object_name,
p_old_column_value => l_database_object_name,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_message=>'database_object_name is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
SELECT 'Y'
into l_dummy
FROM fnd_tables t
where t.table_name = p_style_rec.database_object_name ;
IF (p_create_update_flag = 'C') THEN
HZ_UTILITY_V2PUB.validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'style_name',
p_column_value => p_style_rec.style_name,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
HZ_UTILITY_V2PUB.validate_cannot_update_to_null (
p_column => 'style_name',
p_column_value => p_style_rec.style_name,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_message=>'style_name cannot be set to null during update. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' OR
(p_create_update_flag = 'U' AND
p_style_rec.style_name is not null AND
p_style_rec.style_name <> l_style_name)
THEN
BEGIN
select 'Y' into l_dummy
from HZ_STYLES_TL
where style_name = p_style_rec.style_name
and language = userenv('LANG');
p_create_update_flag IN VARCHAR2,
p_style_format_rec IN HZ_STYLE_FORMAT_V2PUB.STYLE_FORMAT_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 b.style_format_code,
b.style_code,
b.default_flag,
t.style_format_name
INTO l_style_format_code,
l_style_code,
l_default_flag,
l_style_format_name
FROM HZ_STYLE_FORMATS_B b , HZ_STYLE_FORMATS_TL t
WHERE b.ROWID = p_rowid
AND t.style_format_code = b.style_format_code
AND t.language = userenv('LANG');
IF (p_create_update_flag = 'C') THEN
HZ_UTILITY_V2PUB.validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'style_format_code',
p_column_value => p_style_format_rec.style_format_code,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' AND
p_style_format_rec.style_format_code IS NOT NULL
THEN
HZ_UTILITY_V2PUB.validate_nonupdateable (
p_column => 'style_format_code',
p_column_value => p_style_format_rec.style_format_code,
p_old_column_value => l_style_format_code,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_message=>'l_style_format_code is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' AND
p_style_format_rec.style_format_code IS NOT NULL
THEN
BEGIN
select 'Y' into l_dummy
from HZ_STYLE_FORMATS_B
where style_format_code = p_style_format_rec.style_format_code;
IF (p_create_update_flag = 'C') THEN
HZ_UTILITY_V2PUB.validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'style_code',
p_column_value => p_style_format_rec.style_code,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' AND
p_style_format_rec.style_code IS NOT NULL
THEN
HZ_UTILITY_V2PUB.validate_nonupdateable (
p_column => 'style_code',
p_column_value => p_style_format_rec.style_code,
p_old_column_value => l_style_code,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_message=>'style_code is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C'
AND
p_style_format_rec.style_code IS NOT NULL
AND
p_style_format_rec.style_code <> fnd_api.g_miss_CHAR
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM HZ_STYLES_B
WHERE style_code = p_style_format_rec.style_code;
IF (p_create_update_flag = 'C') THEN
HZ_UTILITY_V2PUB.validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'default_flag',
p_column_value => p_style_format_rec.default_flag,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
HZ_UTILITY_V2PUB.validate_cannot_update_to_null (
p_column => 'default_flag',
p_column_value => p_style_format_rec.default_flag,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_message=>'default_flag cannot be set to null during update. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'U' AND
p_style_format_rec.default_flag = 'N' AND
l_default_flag = 'Y'
THEN
fnd_message.set_name('AR', 'HZ_STL_FMT_FLAG_NOT_Y_TO_N');
IF (p_create_update_flag = 'C') THEN
HZ_UTILITY_V2PUB.validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'style_format_name',
p_column_value => p_style_format_rec.style_format_name,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
HZ_UTILITY_V2PUB.validate_cannot_update_to_null (
p_column => 'style_format_name',
p_column_value => p_style_format_rec.style_format_name,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_message=>'style_format_name cannot be set to null during update. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' OR
(p_create_update_flag = 'U' AND
p_style_format_rec.style_format_name is not null AND
p_style_format_rec.style_format_name <> l_style_format_name)
THEN
BEGIN
select 'Y' into l_dummy
from HZ_STYLE_FORMATS_TL
where style_format_name = p_style_format_rec.style_format_name
and language = userenv('LANG');
p_create_update_flag IN VARCHAR2,
p_style_fmt_locale_rec IN HZ_STYLE_FMT_LOCALE_V2PUB.STYLE_FMT_LOCALE_REC_TYPE,
p_rowid IN ROWID,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_dummy VARCHAR2(1);
l_updated_stl_fmt_rec HZ_STYLE_FMT_LOCALE_V2PUB.STYLE_FMT_LOCALE_REC_TYPE;
SELECT 'Y'
FROM hz_style_fmt_locales hsfl
WHERE hsfl.style_fmt_locale_id = p_style_fmt_locale_id;
IF (p_create_update_flag = 'U') THEN
SELECT style_fmt_locale_id,
style_format_code,
language_code,
territory_code,
start_date_active,
end_date_active
INTO
l_style_fmt_locale_id,
l_style_format_code,
l_language_code,
l_territory_code,
l_start_date_active,
l_end_date_active
FROM HZ_STYLE_FMT_LOCALES
WHERE ROWID = p_rowid;
IF p_create_update_flag = 'C' THEN
IF p_style_fmt_locale_rec.style_fmt_locale_id IS NOT NULL AND
p_style_fmt_locale_rec.style_fmt_locale_id <> fnd_api.g_miss_num
THEN
OPEN c_dup (p_style_fmt_locale_rec.style_fmt_locale_id);
IF (p_create_update_flag = 'C') THEN
HZ_UTILITY_V2PUB.validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'style_format_code',
p_column_value => p_style_fmt_locale_rec.style_format_code,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' AND
p_style_fmt_locale_rec.style_format_code IS NOT NULL
THEN
HZ_UTILITY_V2PUB.validate_nonupdateable (
p_column => 'style_format_code',
p_column_value => p_style_fmt_locale_rec.style_format_code,
p_old_column_value => l_style_format_code,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_message=>'style_format_code is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C'
AND
p_style_fmt_locale_rec.style_format_code IS NOT NULL
AND
p_style_fmt_locale_rec.style_format_code <> fnd_api.g_miss_CHAR
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM HZ_STYLE_FORMATS_B
WHERE style_format_code = p_style_fmt_locale_rec.style_format_code;
IF p_create_update_flag = 'U' AND
p_style_fmt_locale_rec.language_code IS NOT NULL
THEN
HZ_UTILITY_V2PUB.validate_nonupdateable (
p_column => 'language_code',
p_column_value => p_style_fmt_locale_rec.language_code,
p_old_column_value => l_language_code,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_message=>'language_code is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C'
AND
p_style_fmt_locale_rec.language_code IS NOT NULL
AND
p_style_fmt_locale_rec.language_code <> fnd_api.g_miss_CHAR
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM FND_LANGUAGES
WHERE language_code = p_style_fmt_locale_rec.language_code;
IF p_create_update_flag = 'U' AND
p_style_fmt_locale_rec.territory_code IS NOT NULL
THEN
HZ_UTILITY_V2PUB.validate_nonupdateable (
p_column => 'territory_code',
p_column_value => p_style_fmt_locale_rec.territory_code,
p_old_column_value => l_territory_code,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_message=>'territory_code is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C'
AND
p_style_fmt_locale_rec.territory_code IS NOT NULL
AND
p_style_fmt_locale_rec.territory_code <> fnd_api.g_miss_CHAR
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM FND_TERRITORIES
WHERE territory_code = p_style_fmt_locale_rec.territory_code;
IF p_create_update_flag = 'C' AND
not ((p_style_fmt_locale_rec.language_code IS NOT NULL AND
(p_style_fmt_locale_rec.language_code <> fnd_api.g_miss_char) OR
(p_style_fmt_locale_rec.territory_code IS NOT NULL ) AND
p_style_fmt_locale_rec.territory_code <> fnd_api.g_miss_char ) )
THEN
FND_MESSAGE.SET_NAME('AR', 'HZ_STL_FMT_LOC_MISSING_COLUMN');
IF (p_create_update_flag = 'C') THEN
HZ_UTILITY_V2PUB.validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'start_date_active',
p_column_value => p_style_fmt_locale_rec.start_date_active,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
HZ_UTILITY_V2PUB.validate_cannot_update_to_null (
p_column => 'start_date_active',
p_column_value => p_style_fmt_locale_rec.start_date_active,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_message=>'start_date_active cannot be set to null during update. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF (p_create_update_flag = 'C') THEN
IF p_style_fmt_locale_rec.end_date_active IS NOT NULL AND
p_style_fmt_locale_rec.end_date_active <> fnd_api.g_miss_date AND
p_style_fmt_locale_rec.end_date_active < p_style_fmt_locale_rec.start_date_active
THEN
fnd_message.set_name('AR', 'HZ_API_START_DATE_GREATER');
ELSIF (p_create_update_flag = 'U') THEN
-- old start_date_active, end_date_active has been selected from table
-- and put into l_start_date_active, l_end_date_active
IF p_style_fmt_locale_rec.start_date_active <> fnd_api.g_miss_date
AND p_style_fmt_locale_rec.start_date_active is not null
THEN
l_start_date_active := p_style_fmt_locale_rec.start_date_active;
IF p_create_update_flag = 'C' AND
p_style_fmt_locale_rec.style_format_code IS NOT NULL AND
( p_style_fmt_locale_rec.language_code IS NOT NULL OR
p_style_fmt_locale_rec.territory_code IS NOT NULL )
THEN
BEGIN
select style_fmt_locale_id into l_dup_style_fmt_locale_id
from HZ_STYLE_FMT_LOCALES
where style_format_code = p_style_fmt_locale_rec.style_format_code AND
decode(language_code, null, fnd_api.g_miss_char, language_code) = NVL( p_style_fmt_locale_rec.language_code, fnd_api.g_miss_char) AND
decode(territory_code, null, fnd_api.g_miss_char, territory_code) = NVL(p_style_fmt_locale_rec.territory_code, fnd_api.g_miss_char) AND
NOT ( ( p_style_fmt_locale_rec.end_date_active is not null and
p_style_fmt_locale_rec.end_date_active <> fnd_api.g_miss_date and
p_style_fmt_locale_rec.end_date_active < start_date_active ) OR
( end_date_active is not null and
p_style_fmt_locale_rec.start_date_active > end_date_active )) AND
rownum =1;
ELSIF p_create_update_flag = 'U' AND
(p_style_fmt_locale_rec.start_date_active is not null OR
p_style_fmt_locale_rec.end_date_active is not null )
THEN
get_updated_record (
p_style_fmt_locale_id => p_style_fmt_locale_rec.style_fmt_locale_id,
p_update_field_rec => p_style_fmt_locale_rec,
x_updated_rec => l_updated_stl_fmt_rec );
select style_fmt_locale_id into l_dup_style_fmt_locale_id
from HZ_STYLE_FMT_LOCALES
where style_fmt_locale_id <> l_updated_stl_fmt_rec.style_fmt_locale_id AND
style_format_code = l_updated_stl_fmt_rec.style_format_code AND
decode(language_code, null, fnd_api.g_miss_char, language_code) = NVL( l_updated_stl_fmt_rec.language_code, fnd_api.g_miss_char) AND
decode(territory_code, null, fnd_api.g_miss_char, territory_code) = NVL(l_updated_stl_fmt_rec.territory_code, fnd_api.g_miss_char) AND
NOT ( ( l_updated_stl_fmt_rec.end_date_active is not null and
l_updated_stl_fmt_rec.end_date_active < start_date_active ) OR
( end_date_active is not null and
l_updated_stl_fmt_rec.start_date_active > end_date_active )) AND
rownum =1;
p_create_update_flag IN VARCHAR2,
p_style_fmt_variation_rec IN HZ_STYLE_FMT_VARIATION_V2PUB.STYLE_FMT_VARIATION_REC_TYPE,
p_rowid IN ROWID,
x_return_status IN OUT NOCOPY VARCHAR2
)IS
l_style_format_code HZ_STYLE_FMT_VARIATIONS.style_format_code%TYPE;
IF (p_create_update_flag = 'U') THEN
SELECT
style_format_code,
variation_number,
variation_rank,
start_date_active,
end_date_active
INTO
l_style_format_code,
l_variation_number,
l_variation_rank,
l_start_date_active,
l_end_date_active
FROM HZ_STYLE_FMT_VARIATIONS
WHERE ROWID = p_rowid;
IF (p_create_update_flag = 'C') THEN
HZ_UTILITY_V2PUB.validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'style_format_code',
p_column_value => p_style_fmt_variation_rec.style_format_code,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' AND
p_style_fmt_variation_rec.style_format_code IS NOT NULL
THEN
HZ_UTILITY_V2PUB.validate_nonupdateable (
p_column => 'style_format_code',
p_column_value => p_style_fmt_variation_rec.style_format_code,
p_old_column_value => l_style_format_code,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_message=>'l_style_format_code is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C'
AND
p_style_fmt_variation_rec.style_format_code IS NOT NULL
AND
p_style_fmt_variation_rec.style_format_code <> fnd_api.g_miss_CHAR
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM HZ_STYLE_FORMATS_B
WHERE style_format_code = p_style_fmt_variation_rec.style_format_code;
IF (p_create_update_flag = 'C') THEN
HZ_UTILITY_V2PUB.validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'variation_number',
p_column_value => p_style_fmt_variation_rec.variation_number,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' AND
p_style_fmt_variation_rec.variation_number IS NOT NULL
THEN
HZ_UTILITY_V2PUB.validate_nonupdateable (
p_column => 'variation_number',
p_column_value => p_style_fmt_variation_rec.variation_number,
p_old_column_value => l_variation_number,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_message=>'l_variation_number is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C'
THEN
BEGIN
select 'Y' into l_dummy
from HZ_STYLE_FMT_VARIATIONS
where variation_number = p_style_fmt_variation_rec.variation_number
and style_format_code = p_style_fmt_variation_rec.style_format_code;
IF p_create_update_flag = 'C' AND
p_style_fmt_variation_rec.variation_number is not null
THEN
check_greater_than_zero (
p_column => 'variation_number',
p_column_value => p_style_fmt_variation_rec.variation_number,
x_return_status => x_return_status );
IF (p_create_update_flag = 'C') THEN
HZ_UTILITY_V2PUB.validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'variation_rank',
p_column_value => p_style_fmt_variation_rec.variation_rank,
x_return_status => x_return_status);
IF p_create_update_flag = 'C' OR
(p_create_update_flag = 'U' AND
p_style_fmt_variation_rec.variation_rank is not null AND
p_style_fmt_variation_rec.variation_rank <> l_variation_rank)
THEN
BEGIN
select 'Y' into l_dummy
from HZ_STYLE_FMT_VARIATIONS
where variation_rank = p_style_fmt_variation_rec.variation_rank
and style_format_code = p_style_fmt_variation_rec.style_format_code;
IF p_create_update_flag = 'U' THEN
HZ_UTILITY_V2PUB.validate_cannot_update_to_null (
p_column => 'variation_rank',
p_column_value => p_style_fmt_variation_rec.variation_rank,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_message=>'variation_rank cannot be set to null during update. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF (p_create_update_flag = 'C' AND
p_style_fmt_variation_rec.variation_rank is not null ) OR
(p_create_update_flag = 'U' AND
p_style_fmt_variation_rec.variation_rank is not null AND
p_style_fmt_variation_rec.variation_rank <> fnd_api.g_miss_num AND
p_style_fmt_variation_rec.variation_rank <> l_variation_rank)
THEN
check_greater_than_zero (
p_column => 'variation_rank',
p_column_value => p_style_fmt_variation_rec.variation_rank,
x_return_status => x_return_status );
IF (p_create_update_flag = 'C') THEN
HZ_UTILITY_V2PUB.validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'start_date_active',
p_column_value => p_style_fmt_variation_rec.start_date_active,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
HZ_UTILITY_V2PUB.validate_cannot_update_to_null (
p_column => 'start_date_active',
p_column_value => p_style_fmt_variation_rec.start_date_active,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_message=>'start_date_active cannot be set to null during update. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF (p_create_update_flag = 'C') THEN
IF p_style_fmt_variation_rec.end_date_active IS NOT NULL AND
p_style_fmt_variation_rec.end_date_active <> fnd_api.g_miss_date AND
p_style_fmt_variation_rec.end_date_active < p_style_fmt_variation_rec.start_date_active
THEN
fnd_message.set_name('AR', 'HZ_API_START_DATE_GREATER');
ELSIF (p_create_update_flag = 'U') THEN
-- old start_date_active, end_date_active has been selected from table
-- and put into l_start_date_active, l_end_date_active
IF p_style_fmt_variation_rec.start_date_active <> fnd_api.g_miss_date
AND p_style_fmt_variation_rec.start_date_active is not null
THEN
l_start_date_active := p_style_fmt_variation_rec.start_date_active;
p_create_update_flag IN VARCHAR2,
p_style_fmt_layout_rec IN HZ_STYLE_FMT_LAYOUT_V2PUB.STYLE_FMT_LAYOUT_REC_TYPE,
p_rowid IN ROWID,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_dummy VARCHAR2(1);
l_updated_stl_fmt_layout_rec HZ_STYLE_FMT_LAYOUT_V2PUB.STYLE_FMT_LAYOUT_REC_TYPE;
SELECT 'Y'
FROM hz_style_fmt_layouts_b hsfl
WHERE hsfl.style_fmt_layout_id = p_style_fmt_layout_id;
IF (p_create_update_flag = 'U') THEN
SELECT
style_fmt_layout_id,
style_format_code,
variation_number,
attribute_code,
attribute_application_id,
line_number,
position,
mandatory_flag,
use_initial_flag,
uppercase_flag,
blank_lines_before,
blank_lines_after,
start_date_active,
end_date_active
INTO
l_style_fmt_layout_id,
l_style_format_code,
l_variation_number,
l_attribute_code,
l_attribute_application_id,
l_line_number,
l_position,
l_mandatory_flag,
l_use_initial_flag,
l_uppercase_flag,
l_blank_lines_before,
l_blank_lines_after,
l_start_date_active,
l_end_date_active
FROM HZ_STYLE_FMT_LAYOUTS_B
WHERE ROWID = p_rowid;
get_updated_record (
p_style_fmt_layout_id => p_style_fmt_layout_rec.style_fmt_layout_id,
p_update_field_rec => p_style_fmt_layout_rec,
x_updated_rec => l_updated_stl_fmt_layout_rec );
IF p_create_update_flag = 'C' THEN
IF p_style_fmt_layout_rec.style_fmt_layout_id IS NOT NULL AND
p_style_fmt_layout_rec.style_fmt_layout_id <> fnd_api.g_miss_num
THEN
OPEN c_dup (p_style_fmt_layout_rec.style_fmt_layout_id);
IF (p_create_update_flag = 'C') THEN
HZ_UTILITY_V2PUB.validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'style_format_code',
p_column_value => p_style_fmt_layout_rec.style_format_code,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' AND
p_style_fmt_layout_rec.style_format_code IS NOT NULL
THEN
HZ_UTILITY_V2PUB.validate_nonupdateable (
p_column => 'style_format_code',
p_column_value => p_style_fmt_layout_rec.style_format_code,
p_old_column_value => l_style_format_code,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_message=>'l_style_format_code is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF (p_create_update_flag = 'C') THEN
HZ_UTILITY_V2PUB.validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'variation_number',
p_column_value => p_style_fmt_layout_rec.variation_number,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' AND
p_style_fmt_layout_rec.variation_number IS NOT NULL
THEN
HZ_UTILITY_V2PUB.validate_nonupdateable (
p_column => 'variation_number',
p_column_value => p_style_fmt_layout_rec.variation_number,
p_old_column_value => l_variation_number,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_message=>'l_variation_number is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' AND
p_style_fmt_layout_rec.variation_number is not null
THEN
check_greater_than_zero (
p_column => 'variation_number',
p_column_value => p_style_fmt_layout_rec.variation_number,
x_return_status => x_return_status );
IF p_create_update_flag = 'C' AND
p_style_fmt_layout_rec.style_format_code IS NOT NULL AND
p_style_fmt_layout_rec.style_format_code <> fnd_api.g_miss_CHAR AND
p_style_fmt_layout_rec.variation_number IS NOT NULL AND
p_style_fmt_layout_rec.variation_number <> fnd_api.g_miss_num
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM HZ_STYLE_FMT_VARIATIONS
WHERE style_format_code = p_style_fmt_layout_rec.style_format_code AND
variation_number = p_style_fmt_layout_rec.variation_number;
SELECT 'Y'
INTO l_dummy
FROM HZ_STYLE_FMT_VARIATIONS
WHERE style_format_code = p_style_fmt_layout_rec.style_format_code AND
variation_number = p_style_fmt_layout_rec.variation_number AND
p_style_fmt_layout_rec.start_date_active
BETWEEN start_date_active AND NVL(end_date_active, to_date('12/31/4712','MM/DD/YYYY')) AND
(decode(p_style_fmt_layout_rec.end_date_active, null, to_date('12/31/4712','MM/DD/YYYY'),
fnd_api.g_miss_date, to_date('12/31/4712','MM/DD/YYYY'), p_style_fmt_layout_rec.end_date_active)
BETWEEN start_date_active
AND NVL(end_date_active, to_date('12/31/4712','MM/DD/YYYY')));
ELSIF p_create_update_flag = 'U' AND
x_return_status = fnd_api.g_ret_sts_success
THEN
BEGIN
SELECT 'Y'
INTO l_dummy
FROM HZ_STYLE_FMT_VARIATIONS
WHERE style_format_code = l_updated_stl_fmt_layout_rec.style_format_code AND
variation_number = l_updated_stl_fmt_layout_rec.variation_number AND
l_updated_stl_fmt_layout_rec.start_date_active BETWEEN start_date_active
AND NVL(end_date_active, to_date('12/31/4712','MM/DD/YYYY')) AND
(decode(l_updated_stl_fmt_layout_rec.end_date_active, null, to_date('12/31/4712','MM/DD/YYYY'),
fnd_api.g_miss_date, to_date('12/31/4712','MM/DD/YYYY'), l_updated_stl_fmt_layout_rec.end_date_active)
BETWEEN start_date_active
AND NVL(end_date_active, to_date('12/31/4712','MM/DD/YYYY')));
IF (p_create_update_flag = 'C') THEN
HZ_UTILITY_V2PUB.validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'attribute_code',
p_column_value => p_style_fmt_layout_rec.attribute_code,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' AND
p_style_fmt_layout_rec.attribute_code IS NOT NULL
THEN
HZ_UTILITY_V2PUB.validate_nonupdateable (
p_column => 'attribute_code',
p_column_value => p_style_fmt_layout_rec.attribute_code,
p_old_column_value => l_attribute_code,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_message=>'attribute_code is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF (p_create_update_flag = 'C') THEN
HZ_UTILITY_V2PUB.validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'attribute_application_id',
p_column_value => p_style_fmt_layout_rec.attribute_application_id,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' AND
p_style_fmt_layout_rec.attribute_application_id IS NOT NULL
THEN
HZ_UTILITY_V2PUB.validate_nonupdateable (
p_column => 'attribute_application_id',
p_column_value => p_style_fmt_layout_rec.attribute_application_id,
p_old_column_value => l_attribute_application_id,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_message=>'attribute_application_id is non-updateable. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
p_create_update_flag = 'C' AND
p_style_fmt_layout_rec.attribute_code IS NOT NULL AND
p_style_fmt_layout_rec.attribute_code <> fnd_api.g_miss_CHAR
-- p_style_fmt_layout_rec.attribute_application_id IS NOT NULL AND
-- p_style_fmt_layout_rec.attribute_application_id <> fnd_api.g_miss_num
THEN
BEGIN
SELECT database_object_name
INTO l_database_object_name
FROM hz_style_formats_b sf, hz_styles_b s
WHERE sf.style_format_code = p_style_fmt_layout_rec.style_format_code AND
s.style_code = sf.style_code;
SELECT 'Y'
into l_dummy
FROM fnd_columns c, fnd_tables t
where t.table_name = l_database_object_name AND
t.table_id = c.table_id AND
c.application_id = t.application_id AND --Bug No.4942505. SQLID:14450634
c.column_name = p_style_fmt_layout_rec.attribute_code;
IF p_create_update_flag = 'C' AND
p_style_fmt_layout_rec.style_format_code IS NOT NULL AND
p_style_fmt_layout_rec.variation_number IS NOT NULL AND
p_style_fmt_layout_rec.attribute_code IS NOT NULL AND
p_style_fmt_layout_rec.attribute_application_id IS NOT NULL
THEN
BEGIN
select style_fmt_layout_id into l_dup_style_fmt_layout_id
from HZ_STYLE_FMT_LAYOUTS_B
where style_format_code = p_style_fmt_layout_rec.style_format_code AND
variation_number = p_style_fmt_layout_rec.variation_number AND
attribute_code = p_style_fmt_layout_rec.attribute_code AND
attribute_application_id = p_style_fmt_layout_rec.attribute_application_id AND
NOT ( ( p_style_fmt_layout_rec.end_date_active is not null and
p_style_fmt_layout_rec.end_date_active <> fnd_api.g_miss_date and
p_style_fmt_layout_rec.end_date_active < start_date_active ) OR
( end_date_active is not null and
p_style_fmt_layout_rec.start_date_active > end_date_active )) AND
rownum =1;
ELSIF p_create_update_flag = 'U' AND
(p_style_fmt_layout_rec.start_date_active is not null OR
p_style_fmt_layout_rec.end_date_active is not null )
THEN
BEGIN
select style_fmt_layout_id into l_dup_style_fmt_layout_id
from HZ_STYLE_FMT_LAYOUTS_B
where style_fmt_layout_id <> l_updated_stl_fmt_layout_rec.style_fmt_layout_id AND
style_format_code = l_updated_stl_fmt_layout_rec.style_format_code AND
variation_number = l_updated_stl_fmt_layout_rec.variation_number AND
attribute_code = l_updated_stl_fmt_layout_rec.attribute_code AND
attribute_application_id = l_updated_stl_fmt_layout_rec.attribute_application_id AND
NOT ( ( l_updated_stl_fmt_layout_rec.end_date_active is not null and
l_updated_stl_fmt_layout_rec.end_date_active < start_date_active ) OR
( end_date_active is not null and
l_updated_stl_fmt_layout_rec.start_date_active > end_date_active )) AND
rownum =1;
IF (p_create_update_flag = 'C') THEN
HZ_UTILITY_V2PUB.validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'line_number ',
p_column_value => p_style_fmt_layout_rec.line_number ,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
HZ_UTILITY_V2PUB.validate_cannot_update_to_null (
p_column => 'line_number',
p_column_value => p_style_fmt_layout_rec.line_number,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_message=>'line_number cannot be set to null during update. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF (p_create_update_flag = 'C') THEN
HZ_UTILITY_V2PUB.validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'position ',
p_column_value => p_style_fmt_layout_rec.position ,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
HZ_UTILITY_V2PUB.validate_cannot_update_to_null (
p_column => 'position',
p_column_value => p_style_fmt_layout_rec.position,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_message=>'position cannot be set to null during update. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF p_create_update_flag = 'C' AND
p_style_fmt_layout_rec.style_format_code IS NOT NULL AND
p_style_fmt_layout_rec.variation_number IS NOT NULL AND
p_style_fmt_layout_rec.line_number IS NOT NULL AND
p_style_fmt_layout_rec.position IS NOT NULL
THEN
BEGIN
select style_fmt_layout_id into l_dup_style_fmt_layout_id
from HZ_STYLE_FMT_LAYOUTS_B
where style_format_code = p_style_fmt_layout_rec.style_format_code AND
variation_number = p_style_fmt_layout_rec.variation_number AND
line_number = p_style_fmt_layout_rec.line_number AND
position = p_style_fmt_layout_rec.position AND
NOT ( ( p_style_fmt_layout_rec.end_date_active is not null and
p_style_fmt_layout_rec.end_date_active <> fnd_api.g_miss_date and
p_style_fmt_layout_rec.end_date_active < start_date_active ) OR
( end_date_active is not null and
p_style_fmt_layout_rec.start_date_active > end_date_active )) AND
rownum =1;
ELSIF p_create_update_flag = 'U' AND
(p_style_fmt_layout_rec.start_date_active is not null OR
p_style_fmt_layout_rec.end_date_active is not null )
THEN
BEGIN
select style_fmt_layout_id into l_dup_style_fmt_layout_id
from HZ_STYLE_FMT_LAYOUTS_B
where style_fmt_layout_id <> l_updated_stl_fmt_layout_rec.style_fmt_layout_id AND
style_format_code = l_updated_stl_fmt_layout_rec.style_format_code AND
variation_number = l_updated_stl_fmt_layout_rec.variation_number AND
line_number = l_updated_stl_fmt_layout_rec.line_number AND
position = l_updated_stl_fmt_layout_rec.position AND
NOT ( ( l_updated_stl_fmt_layout_rec.end_date_active is not null and
l_updated_stl_fmt_layout_rec.end_date_active < start_date_active ) OR
( end_date_active is not null and
l_updated_stl_fmt_layout_rec.start_date_active > end_date_active )) AND
rownum =1;
IF (p_create_update_flag = 'C') THEN
HZ_UTILITY_V2PUB.validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'mandatory_flag',
p_column_value => p_style_fmt_layout_rec.mandatory_flag ,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
HZ_UTILITY_V2PUB.validate_cannot_update_to_null (
p_column => 'mandatory_flag',
p_column_value => p_style_fmt_layout_rec.mandatory_flag,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_message=>'mandatory_flag cannot be set to null during update. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF (p_create_update_flag = 'C') THEN
HZ_UTILITY_V2PUB.validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'use_initial_flag',
p_column_value => p_style_fmt_layout_rec.use_initial_flag ,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
HZ_UTILITY_V2PUB.validate_cannot_update_to_null (
p_column => 'use_initial_flag',
p_column_value => p_style_fmt_layout_rec.use_initial_flag,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_message=>'use_initial_flag cannot be set to null during update. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF (p_create_update_flag = 'C') THEN
HZ_UTILITY_V2PUB.validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'uppercase_flag',
p_column_value => p_style_fmt_layout_rec.uppercase_flag ,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
HZ_UTILITY_V2PUB.validate_cannot_update_to_null (
p_column => 'uppercase_flag',
p_column_value => p_style_fmt_layout_rec.uppercase_flag,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_message=>'uppercase_flag cannot be set to null during update. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF (p_create_update_flag = 'C') THEN
HZ_UTILITY_V2PUB.validate_mandatory (
p_create_update_flag => p_create_update_flag,
p_column => 'start_date_active',
p_column_value => p_style_fmt_layout_rec.start_date_active,
x_return_status => x_return_status);
IF p_create_update_flag = 'U' THEN
HZ_UTILITY_V2PUB.validate_cannot_update_to_null (
p_column => 'start_date_active',
p_column_value => p_style_fmt_layout_rec.start_date_active,
x_return_status => x_return_status);
hz_utility_v2pub.debug(p_message=>'start_date_active cannot be set to null during update. ' ||
'x_return_status = ' || x_return_status,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
IF (p_create_update_flag = 'C') THEN
IF p_style_fmt_layout_rec.end_date_active IS NOT NULL AND
p_style_fmt_layout_rec.end_date_active <> fnd_api.g_miss_date AND
p_style_fmt_layout_rec.end_date_active < p_style_fmt_layout_rec.start_date_active
THEN
fnd_message.set_name('AR', 'HZ_API_START_DATE_GREATER');
ELSIF (p_create_update_flag = 'U') THEN
-- old start_date_active, end_date_active has been selected from table
-- and put into l_start_date_active, l_end_date_active
IF p_style_fmt_layout_rec.start_date_active <> fnd_api.g_miss_date
AND p_style_fmt_layout_rec.start_date_active is not null
THEN
l_start_date_active := p_style_fmt_layout_rec.start_date_active;