[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
( create_update_flag IN VARCHAR2,
p_col_name IN VARCHAR2,
p_col_val IN VARCHAR2,
p_miss_allowed_in_c IN BOOLEAN,
p_miss_allowed_in_u IN BOOLEAN,
x_return_status IN OUT NOCOPY VARCHAR2)
IS
BEGIN
IF (p_col_val = FND_API.G_MISS_CHAR) THEN
fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
IF (create_update_flag = 'C') THEN
IF ((NOT p_miss_allowed_in_c) AND
p_col_val IS NULL )
THEN
fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
( create_update_flag IN VARCHAR2,
p_col_name IN VARCHAR2,
p_col_val IN DATE,
p_miss_allowed_in_c IN BOOLEAN,
p_miss_allowed_in_u IN BOOLEAN,
x_return_status IN OUT NOCOPY VARCHAR2)
IS
BEGIN
IF (p_col_val = FND_API.G_MISS_DATE) THEN
fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
IF (create_update_flag = 'C') THEN
IF ((NOT p_miss_allowed_in_c) AND
p_col_val IS NULL )
THEN
fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
( create_update_flag IN VARCHAR2,
p_col_name IN VARCHAR2,
p_col_val IN NUMBER,
p_miss_allowed_in_c IN BOOLEAN,
p_miss_allowed_in_u IN BOOLEAN,
x_return_status IN OUT NOCOPY VARCHAR2)
IS
BEGIN
IF (p_col_val = FND_API.G_MISS_NUM) THEN
fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
IF (create_update_flag = 'C') THEN
IF ((NOT p_miss_allowed_in_c) AND
p_col_val IS NULL )
THEN
fnd_message.set_name('AR', 'HZ_API_MISSING_COLUMN');
SELECT 'Y'
FROM fnd_lookup_values
WHERE lookup_type = p_lookup_type
AND lookup_code = p_column_value
-- bug 4212585
AND enabled_flag = 'Y'
AND sysdate between nvl(start_date_active,sysdate) AND nvl(end_date_active,sysdate+1)
AND ROWNUM = 1;
SELECT 'Y'
FROM fnd_lookup_values
WHERE lookup_type = p_lookup_type
AND replace(lookup_code, '.', '') = replace(p_column_value, '.', '')
-- bug 4212585
AND enabled_flag = 'Y'
AND sysdate between nvl(start_date_active,sysdate) AND nvl(end_date_active,sysdate+1)
AND ROWNUM = 1;
select 'x'
from fnd_lookup_values_vl
where lookup_type = p_class_category
and sysdate between start_date_active and nvl(end_date_active,sysdate)
and instrb(meaning,p_delimiter)>0;
select 'x'
from hz_class_categories
where class_category = p_class_category
and instrb(p_meaning,delimiter)>0;
SELECT 'Y'
FROM hz_class_categories
WHERE class_category = p_class_category
AND ROWNUM = 1;
SELECT 'Y'
FROM hz_code_assignments a,
hz_class_code_relations b
WHERE a.class_category = p_class_category
AND b.class_category = p_class_category
AND a.class_code = b.class_code
AND (( a.start_date_active <= SYSDATE
AND NVL(a.end_date_active , SYSDATE) >= SYSDATE )
OR a.start_date_active > SYSDATE )
AND (( b.start_date_active <= SYSDATE
AND NVL(b.end_date_active , SYSDATE) >= SYSDATE )
OR b.start_date_active > SYSDATE )
AND ROWNUM = 1;
SELECT start_date_active,
end_date_active
FROM hz_class_code_relations
WHERE class_category = p_class_category
AND class_code = p_sub_class_code
AND sub_class_code = p_class_code
AND (( NVL(end_date_active , SYSDATE) >= SYSDATE
AND NVL(start_date_active, SYSDATE) <= SYSDATE)
OR start_date_active > SYSDATE );
SELECT sub_class_code,
start_date_active,
end_date_active
FROM hz_class_code_relations
WHERE class_category = p_class_category;
SELECT class_code,
start_date_active,
end_date_active
FROM hz_class_code_relations
WHERE class_category = p_class_category
AND sub_class_code = p_sub_class_code;
SELECT DISTINCT actual_content_source,
owner_table_name,
owner_table_id
FROM hz_code_assignments ca, fnd_lookup_values_vl lv
WHERE ca.class_category = p_class_category
AND ca.class_category = lv.lookup_type
AND ca.class_code = lv.lookup_code;
SELECT class_code,
start_date_active,
end_date_active
FROM hz_code_assignments
WHERE class_category = p_class_category
AND actual_content_source = l_content_source_type
AND owner_table_name = l_owner_table_name
AND owner_table_id = l_owner_table_id;
SELECT 'Y'
FROM hz_class_categories
WHERE class_category = p_class_category;
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;
create_update_flag IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_count NUMBER;
SELECT 1
FROM fnd_lookup_types
WHERE lookup_type = p_class_cat_rec.class_category
AND rownum = 1;
select created_by_module
from hz_class_categories
where class_category = p_class_cat_rec.class_category;
IF create_update_flag = 'U' THEN
OPEN c_categories;
check_mandatory_str_col(create_update_flag, 'class_category',
p_class_cat_rec.class_category,
FALSE,
FALSE, -- cannot be missing: PK
x_return_status);
check_mandatory_str_col(create_update_flag, 'allow_multi_assign_flag',
p_class_cat_rec.allow_multi_assign_flag,
FALSE,
TRUE,
x_return_status);
check_mandatory_str_col(create_update_flag, 'allow_multi_parent_flag',
p_class_cat_rec.allow_multi_parent_flag,
FALSE,
TRUE,
x_return_status);
check_mandatory_str_col(create_update_flag, 'allow_leaf_node_only_flag',
p_class_cat_rec.allow_leaf_node_only_flag,
FALSE,
TRUE,
x_return_status);
p_create_update_flag => create_update_flag,
p_created_by_module => p_class_cat_rec.created_by_module,
p_old_created_by_module => l_created_by_module,
x_return_status => x_return_status);
IF create_update_flag = 'C' THEN
IF exist_class_category(p_class_cat_rec.class_category) = 'Y' THEN
fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
IF create_update_flag = 'U' THEN
IF ( (p_class_cat_rec.allow_leaf_node_only_flag = 'Y' )
AND (exist_code_ass_not_node(p_class_cat_rec.class_category) = 'Y' ) )
THEN
fnd_message.set_name('AR', 'HZ_API_LEAF_ONLY_NOT_ALLOWED');
( p_create_update_flag IN VARCHAR2,
p_class_category IN VARCHAR2,
p_owner_table IN VARCHAR2 )
RETURN VARCHAR2
IS
CURSOR c_nb(
p_class_category IN VARCHAR2,
p_owner_table IN VARCHAR2)
IS
SELECT COUNT(1)
FROM hz_class_category_uses
WHERE class_category = p_class_category
AND owner_table = p_owner_table;
IF ( (p_create_update_flag = 'C' AND l_count <> 0 )
OR (p_create_update_flag = 'U' AND l_count > 1 ))
THEN
result := 'Y';
create_update_flag IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2 )
IS
l_end_date DATE := NULL;
select created_by_module
from hz_class_category_uses
where class_category = p_in_rec.class_category
and owner_table = p_in_rec.owner_table;
IF create_update_flag = 'U' THEN
OPEN c_uses;
create_update_flag,
'class_category',
p_in_rec.class_category,
FALSE,
FALSE,
x_return_status);
create_update_flag,
'owner_table',
p_in_rec.owner_table,
FALSE,
FALSE,
x_return_status);
p_create_update_flag => create_update_flag,
p_created_by_module => p_in_rec.created_by_module,
p_old_created_by_module => l_created_by_module,
x_return_status => x_return_status);
select '1' into xx_obj
from fnd_objects
where obj_name = p_in_rec.owner_table --Bug NO.:4942331 SQLID:14450613
and ( p_in_rec.column_name is null
or nvl(pk1_column_name,'-999') = nvl(p_in_rec.column_name,'-999') );
IF ( existence_couple_clacat_owntab( create_update_flag,
p_in_rec.class_category,
p_in_rec.owner_table) = 'Y' ) THEN
fnd_message.set_name('AR','HZ_API_USE_ONCE_OWNER_TABLE');
SELECT 'Y'
FROM fnd_lookup_values
WHERE lookup_type = p_class_category
AND lookup_code = p_class_code
AND NVL(end_date_active, p_start_date_active) >= p_start_date_active
AND start_date_active <= p_start_date_active;
SELECT class_code,
start_date_active,
end_date_active
FROM hz_code_assignments
WHERE owner_table_name = p_owner_table_name
AND owner_table_id = p_owner_table_id
AND class_category = p_class_category
AND actual_content_source = p_content_source_type
AND ( NVL(end_date_active, p_start_date_active) >= p_start_date_active
OR start_date_active <= NVL(p_end_date_active, start_date_active) )
AND ROWNUM = 1;
SELECT allow_leaf_node_only_flag
FROM hz_class_categories
WHERE class_category = p_class_category;
SELECT allow_multi_assign_flag
FROM hz_class_categories
WHERE class_category = p_class_category;
SELECT column_name,
additional_where_clause
FROM hz_class_category_uses
WHERE upper(class_category) = upper(p_class_category)
AND upper(owner_table) = upper(p_owner_table_name);
x_statement := 'SELECT ' || l_column_name || ' FROM ' || p_owner_table_name || FND_GLOBAL.LOCAL_CHR(10);
SELECT code_assignment_id,
end_date_active
FROM hz_code_assignments
WHERE owner_table_name = p_owner_table_name
AND owner_table_id = p_owner_table_id
AND class_category = p_class_category
AND class_code = p_class_code
AND actual_content_source = p_content_source_type
AND start_date_active = p_start_date_active;
( create_update_flag varchar2,
p_class_category varchar2,
p_owner_table_name varchar2,
p_owner_table_id varchar2,
p_content_source_type varchar2,
p_start_date_active date,
p_end_date_active date,
x_class_code in out NOCOPY varchar2,
x_start_date in out NOCOPY date,
x_end_date in out NOCOPY date )
return varchar2
is
cursor c_create
is
SELECT class_code,
start_date_active,
end_date_active
FROM hz_code_assignments
WHERE owner_table_name = p_owner_table_name
AND owner_table_id = p_owner_table_id
AND class_category = p_class_category
AND actual_content_source = p_content_source_type
AND primary_flag = 'Y'
AND hz_class_validate_v2pub.is_overlap(start_date_active, end_date_active,
p_start_date_active, p_end_date_active) = 'Y';
cursor c_update
is
SELECT class_code,
start_date_active,
end_date_active
FROM hz_code_assignments
WHERE owner_table_name = p_owner_table_name
AND owner_table_id = p_owner_table_id
AND class_category = p_class_category
AND actual_content_source = p_content_source_type
AND primary_flag = 'Y'
AND start_date_active <> p_start_date_active
AND hz_class_validate_v2pub.is_overlap(start_date_active, end_date_active,
p_start_date_active, p_end_date_active) = 'Y';
if create_update_flag = 'C' then
open c_create;
elsif create_update_flag = 'U' then
open c_update;
fetch c_update into x_class_code, x_start_date, x_end_date;
if c_update%notfound then
result := 'N';
close c_update;
( create_update_flag varchar2,
p_class_category varchar2,
p_class_code varchar2,
p_owner_table_name varchar2,
p_owner_table_id varchar2,
p_content_source_type varchar2,
p_start_date_active date,
p_end_date_active date,
x_class_code in out NOCOPY varchar2,
x_start_date in out NOCOPY date,
x_end_date in out NOCOPY date )
return varchar2
is
cursor c_create
is
SELECT class_code,
start_date_active,
end_date_active
FROM hz_code_assignments
WHERE owner_table_name = p_owner_table_name
AND owner_table_id = p_owner_table_id
AND class_category = p_class_category
AND class_code = p_class_code
AND actual_content_source = p_content_source_type
AND hz_class_validate_v2pub.is_overlap(start_date_active, end_date_active,
p_start_date_active, p_end_date_active) = 'Y';
cursor c_update
is
SELECT class_code,
start_date_active,
end_date_active
FROM hz_code_assignments
WHERE owner_table_name = p_owner_table_name
AND owner_table_id = p_owner_table_id
AND class_category = p_class_category
AND class_code = p_class_code
AND actual_content_source = p_content_source_type
AND start_date_active <> p_start_date_active
AND hz_class_validate_v2pub.is_overlap(start_date_active, end_date_active,
p_start_date_active, p_end_date_active) = 'Y';
if create_update_flag = 'C' then
open c_create;
elsif create_update_flag = 'U' then
open c_update;
fetch c_update into x_class_code, x_start_date, x_end_date;
if c_update%notfound then
result := 'N';
close c_update;
( create_update_flag varchar2,
p_class_category varchar2,
p_class_code varchar2,
p_owner_table_name varchar2,
p_owner_table_id varchar2,
p_content_source_type varchar2,
p_start_date_active date,
p_end_date_active date,
x_class_code in out NOCOPY varchar2,
x_start_date in out NOCOPY date,
x_end_date in out NOCOPY date )
return varchar2
is
cursor c_create
is
SELECT class_code,
start_date_active,
end_date_active
FROM hz_code_assignments
WHERE owner_table_name = p_owner_table_name
AND owner_table_id = p_owner_table_id
AND class_category = p_class_category
AND class_code = p_class_code
AND actual_content_source = p_content_source_type
AND primary_flag = 'N'
AND hz_class_validate_v2pub.is_overlap(start_date_active, end_date_active,
p_start_date_active, p_end_date_active) = 'Y';
cursor c_update
is
SELECT class_code,
start_date_active,
end_date_active
FROM hz_code_assignments
WHERE owner_table_name = p_owner_table_name
AND owner_table_id = p_owner_table_id
AND class_category = p_class_category
AND class_code = p_class_code
AND actual_content_source = p_content_source_type
AND start_date_active <> p_start_date_active
AND primary_flag = 'N'
AND hz_class_validate_v2pub.is_overlap(start_date_active, end_date_active,
p_start_date_active, p_end_date_active) = 'Y';
if create_update_flag = 'C' then
open c_create;
elsif create_update_flag = 'U' then
open c_update;
fetch c_update into x_class_code, x_start_date, x_end_date;
if c_update%notfound then
result := 'N';
close c_update;
( p_create_update_flag varchar2,
p_class_category varchar2,
p_class_code varchar2,
p_owner_table_name varchar2,
p_owner_table_id varchar2,
p_content_source_type varchar2,
p_primary_flag varchar2,
p_start_date_active date,
p_end_date_active date,
x_return_status IN OUT NOCOPY VARCHAR2 )
IS
l_class_code varchar2(30);
( p_create_update_flag,
p_class_category ,
p_owner_table_name,
p_owner_table_id ,
p_content_source_type,
p_start_date_active,
p_end_date_active ,
l_class_code ,
l_start_date ,
l_end_date ) = 'Y' then
fnd_message.set_name('AR' , 'HZ_API_DUP_COL_PRIM');
( p_create_update_flag,
p_class_category ,
p_class_code ,
p_owner_table_name,
p_owner_table_id ,
p_content_source_type,
p_start_date_active,
p_end_date_active ,
l_class_code ,
l_start_date ,
l_end_date ) = 'Y'
then
fnd_message.set_name('AR', 'HZ_API_DUP_COD_PRIM_SECOND');
( p_create_update_flag,
p_class_category,
p_class_code ,
p_owner_table_name,
p_owner_table_id ,
p_content_source_type,
p_start_date_active,
p_end_date_active ,
l_class_code ,
l_start_date ,
l_end_date ) = 'Y'
then
fnd_message.set_name('AR', 'HZ_API_DUP_COD_SECOND');
create_update_flag IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
--l_date_active DATE;
SELECT * FROM hz_code_assignments
WHERE owner_table_name = p_owner_table_name AND
owner_table_id = p_owner_table_id AND
class_category = p_class_category AND
status = 'A' AND
--class_code = p_class_code AND
actual_content_source = p_content_source_type;
IF create_update_flag = 'U'
THEN
SELECT class_category,
class_code,
status,
start_date_active,
end_date_active,
owner_table_name,
owner_table_id,
owner_table_key_1,
owner_table_key_2,
owner_table_key_3,
owner_table_key_4,
owner_table_key_5,
content_source_type,
actual_content_source,
created_by_module
INTO l_class_category,
l_class_code,
l_status,
l_start_date_active,
l_end_date_active,
l_owner_table_name,
l_owner_table_id,
l_owner_table_key_1,
l_owner_table_key_2,
l_owner_table_key_3,
l_owner_table_key_4,
l_owner_table_key_5,
l_content_source_type,
l_actual_content_source,
l_created_by_module
FROM hz_code_assignments
WHERE code_assignment_id = p_in_rec.code_assignment_id
AND rownum=1;
create_update_flag,
'code_assignment_id',
p_in_rec.code_assignment_id,
TRUE,
FALSE, -- update needs this to select row
x_return_status);
create_update_flag,
'owner_table_name',
p_in_rec.owner_table_name,
FALSE,
TRUE,
x_return_status);
create_update_flag,
'class_category',
p_in_rec.class_category,
FALSE,
TRUE,
x_return_status);
create_update_flag,
'class_code',
p_in_rec.class_code,
FALSE,
TRUE,
x_return_status);
create_update_flag,
'primary_flag',
p_in_rec.primary_flag,
FALSE,
TRUE,
x_return_status);
create_update_flag,
'content_source_type',
p_in_rec.content_source_type,
FALSE,
TRUE,
x_return_status);*/
p_create_update_flag => create_update_flag,
p_created_by_module => p_in_rec.created_by_module,
p_old_created_by_module => l_created_by_module,
x_return_status => x_return_status);
create_update_flag,
'start_date_active',
p_in_rec.start_date_active,
FALSE,
TRUE,
x_return_status);
IF create_update_flag = 'U' AND
p_in_rec.owner_table_name IS NOT NULL
THEN
validate_nonupdateable (
p_column => 'owner_table_name',
p_column_value => p_in_rec.owner_table_name,
p_old_column_value => l_owner_table_name,
x_return_status => x_return_status);
IF create_update_flag = 'U' AND
p_in_rec.owner_table_id IS NOT NULL
THEN
validate_nonupdateable (
p_column => 'owner_table_id',
p_column_value => p_in_rec.owner_table_id,
p_old_column_value => l_owner_table_id,
x_return_status => x_return_status);
IF create_update_flag = 'U' AND
p_in_rec.owner_table_key_1 IS NOT NULL
THEN
validate_nonupdateable (
p_column => 'owner_table_key_1',
p_column_value => p_in_rec.owner_table_key_1,
p_old_column_value => l_owner_table_key_1,
x_return_status => x_return_status);
IF create_update_flag = 'U' AND
p_in_rec.owner_table_key_2 IS NOT NULL
THEN
validate_nonupdateable (
p_column => 'owner_table_key_2',
p_column_value => p_in_rec.owner_table_key_2,
p_old_column_value => l_owner_table_key_2,
x_return_status => x_return_status);
IF create_update_flag = 'U' AND
p_in_rec.owner_table_key_3 IS NOT NULL
THEN
validate_nonupdateable (
p_column => 'owner_table_key_3',
p_column_value => p_in_rec.owner_table_key_3,
p_old_column_value => l_owner_table_key_3,
x_return_status => x_return_status);
IF create_update_flag = 'U' AND
p_in_rec.owner_table_key_4 IS NOT NULL
THEN
validate_nonupdateable (
p_column => 'owner_table_key_4',
p_column_value => p_in_rec.owner_table_key_4,
p_old_column_value => l_owner_table_key_4,
x_return_status => x_return_status);
IF create_update_flag = 'U' AND
p_in_rec.owner_table_key_5 IS NOT NULL
THEN
validate_nonupdateable (
p_column => 'owner_table_key_5',
p_column_value => p_in_rec.owner_table_key_5,
p_old_column_value => l_owner_table_key_5,
x_return_status => x_return_status);
p_create_update_flag => create_update_flag,
p_check_update_privilege => 'N',
p_content_source_type => p_in_rec.content_source_type,
p_old_content_source_type => l_content_source_type,
p_actual_content_source => p_in_rec.actual_content_source,
p_old_actual_content_source => l_actual_content_source,
p_entity_name => 'HZ_CODE_ASSIGNMENTS',
x_return_status => x_return_status);
IF create_update_flag = 'U' and l_actual_content_source <> 'USER_ENTERED'
THEN
DECLARE
l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
validate_nonupdateable(
p_column => 'CLASS_CATEGORY',
p_column_value => p_in_rec.class_category,
p_old_column_value => l_class_category,
x_return_status => l_return_status,
p_raise_error => 'N');
validate_nonupdateable(
p_column => 'CLASS_CODE',
p_column_value => p_in_rec.class_code,
p_old_column_value => l_class_code,
x_return_status => l_return_status,
p_raise_error => 'N');
HZ_MIXNM_UTILITY.CheckUserUpdatePrivilege(
p_actual_content_source => l_actual_content_source,
p_new_actual_content_source => p_in_rec.actual_content_source,
p_entity_name => 'HZ_CODE_ASSIGNMENTS',
x_return_status => x_return_status);
HZ_UTILITY_V2PUB.G_UPDATE_ACS := 'Y';
IF create_update_flag = 'C'
THEN
SELECT COUNT(1) INTO l_count
FROM hz_class_categories
WHERE class_category = p_in_rec.class_category;
select ALLOW_MULTI_ASSIGN_FLAG into l_allow_multi_assign_flag
from hz_class_categories where
-- Bug 3941471
class_category = nvl(p_in_rec.class_category,l_class_category);
if(create_update_flag = 'C') then
l_end_date_active := nvl(p_in_rec.end_date_active,to_date('4712/12/31','YYYY/MM/DD'));
select count(1) into l_count_multi
from hz_code_assignments
where class_category = p_in_rec.class_category
AND status='A'
AND code_Assignment_id <> nvl(p_in_rec.code_assignment_id, fnd_api.g_miss_num)
AND owner_table_name = nvl(p_in_rec.owner_table_name, l_owner_table_name)
/*
AND ( owner_table_id = nvl(p_in_rec.owner_table_id, l_owner_table_id)
OR
( owner_table_key_1 || owner_table_key_2 ||
owner_table_key_3 || owner_table_key_4 ||
owner_table_key_5 = l_owner_table_keys
)
)
*/
AND ( owner_table_id = l_owner_table_id
AND
( owner_table_key_1 || owner_table_key_2 ||
owner_table_key_3 || owner_table_key_4 ||
owner_table_key_5 is null
)
)
-- Bug 3614582 : Removed TRUNC from the date comparison.
AND is_overlap(nvl(p_in_rec.start_date_active, l_start_Date_active),
nvl(p_in_rec.end_date_active,l_end_date_Active),
START_DATE_ACTIVE, END_DATE_ACTIVE) = 'Y'
/*
AND ((START_DATE_ACTIVE) between (nvl(p_in_rec.start_date_active, l_start_Date_active)) and
(decode(p_in_rec.end_date_active,
fnd_api.g_miss_date,to_date('4712/12/31','YYYY/MM/DD'),
NULL,l_end_date_active,p_in_rec.end_date_active)) OR
(END_DATE_ACTIVE) between (nvl(p_in_rec.start_date_active,l_start_Date_active)) and
(decode(p_in_rec.end_date_active,
fnd_api.g_miss_date,to_date('4712/12/31','YYYY/MM/DD'),
NULL,l_end_date_active,p_in_rec.end_date_active)) OR
(nvl(p_in_rec.start_date_active,l_start_Date_active)) between (START_DATE_ACTIVE) and
(nvl(END_DATE_ACTIVE, to_date('4712/12/31','YYYY/MM/DD'))) OR
(nvl(p_in_rec.end_date_active,l_end_date_Active)) between (START_DATE_ACTIVE) and
(nvl(END_DATE_ACTIVE, to_date('4712/12/31','YYYY/MM/DD')))
)*/;
select count(1) into l_count_multi
from hz_code_assignments
where class_category = p_in_rec.class_category
AND status='A'
AND code_Assignment_id <> nvl(p_in_rec.code_assignment_id, fnd_api.g_miss_num)
AND owner_table_name = nvl(p_in_rec.owner_table_name, l_owner_table_name)
AND ( owner_table_id is null
AND
( nvl(owner_table_key_1 || owner_table_key_2 ||
owner_table_key_3 || owner_table_key_4 ||
owner_table_key_5, fnd_api.g_miss_char) = nvl(l_owner_table_keys, fnd_api.g_miss_char)
)
)
-- Bug 3614582 : Removed TRUNC from the date comparison.
AND is_overlap(nvl(p_in_rec.start_date_active, l_start_Date_active),
nvl(p_in_rec.end_date_active,l_end_date_Active),
START_DATE_ACTIVE, END_DATE_ACTIVE) = 'Y'
/*
AND ((START_DATE_ACTIVE) between (nvl(p_in_rec.start_date_active, l_start_Date_active)) and
(decode(p_in_rec.end_date_active,
fnd_api.g_miss_date,to_date('4712/12/31','YYYY/MM/DD'),
NULL,l_end_date_active,p_in_rec.end_date_active))
OR
(END_DATE_ACTIVE) between (nvl(p_in_rec.start_date_active,l_start_Date_active)) and
(decode(p_in_rec.end_date_active,
fnd_api.g_miss_date,to_date('4712/12/31','YYYY/MM/DD'),
NULL,l_end_date_active,p_in_rec.end_date_active))
OR
(nvl(p_in_rec.start_date_active,l_start_Date_active)) between (START_DATE_ACTIVE) and
(nvl(END_DATE_ACTIVE, to_date('4712/12/31','YYYY/MM/DD'))) OR
(nvl(p_in_rec.end_date_active,l_end_date_Active)) between (START_DATE_ACTIVE) and
(nvl(END_DATE_ACTIVE, to_date('4712/12/31','YYYY/MM/DD')))
)*/
;
IF create_update_flag = 'C' THEN
-- Check start/end active dates
check_start_end_active_dates(
p_in_rec.start_date_active,
p_in_rec.end_date_active,
x_return_status);
select decode(sign(count(*)-1),0,null,'Y')
into allow_leaf_error
from hz_class_code_relations c_rel
, hz_class_categories c_cate
where c_cate.class_category=c_rel.class_category and
allow_leaf_node_only_flag = 'Y' and
--owner_table_name = nvl(p_in_rec.owner_table_name, l_owner_table_name) AND
--owner_table_id = nvl(p_in_rec.owner_table_id, l_owner_table_id) AND
c_cate.class_category = nvl(p_in_rec.class_category, l_class_category) AND
class_code = nvl(p_in_rec.class_code, l_class_code) AND
--primary_flag = p_in_rec.primary_flag AND
start_date_active = nvl(p_in_rec.start_date_active, l_start_date_active) AND
(
(p_in_rec.end_date_active IS NULL) OR
( (nvl(p_in_rec.end_date_active, l_end_date_active) > SYSDATE)
AND (nvl(p_in_rec.end_date_active, l_end_date_active) >= start_date_active)
)
);
FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
select 'Y' into allow_leaf_error
from hz_class_code_relations c_rel, hz_class_categories c_cate
where c_cate.class_category=c_rel.class_category and
allow_leaf_node_only_flag = 'Y' and
c_cate.class_category = nvl(p_in_rec.class_category, l_class_category) AND
class_code = nvl(p_in_rec.class_code, l_class_code) AND
( start_date_active between nvl(p_in_rec.start_date_active, l_start_date_active) and nvl (p_in_rec.end_date_active, l_end_date_active)
OR
nvl(p_in_rec.start_date_active, l_start_date_active) between start_date_active and nvl(end_date_active, to_date('4712/12/31','YYYY/MM/DD'))
) and
rownum = 1;
SELECT code_assignment_id, end_date_active
INTO l_id, l_end_date
FROM hz_code_assignments
WHERE owner_table_name = nvl(p_in_rec.owner_table_name, l_owner_table_name) AND
owner_table_id = nvl(p_in_rec.owner_table_id, l_owner_table_id) AND
class_category = nvl(p_in_rec.class_category, l_class_category) AND
class_code = nvl(p_in_rec.class_code, l_class_code) AND
--primary_flag = p_in_rec.primary_flag AND
actual_content_source = nvl(p_in_rec.actual_content_source, l_actual_content_source) AND
status ='A' AND
-- Bug 3614582 : Removed TRUNC from the date comparison.
(start_date_active) = (nvl(p_in_rec.start_date_active, l_start_date_active)) AND--Bug no 3053541
(
(
(p_in_rec.end_date_active) IS NULL--Bug no 3053541
)
OR
(
(nvl(p_in_rec.end_date_active, l_end_date_active)) --Bug no 3053541
> SYSDATE
AND
(nvl(p_in_rec.end_date_active, l_end_date_active))--Bug no 3053541
>= start_date_active
)
)
AND --Bug no 3053541
(
code_assignment_id <> p_in_rec.code_assignment_id
OR
create_update_flag='C'
);
IF create_update_flag = 'C'
THEN
FND_MESSAGE.SET_NAME('AR', 'HZ_API_DUPLICATE_COLUMN');
FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
IF create_update_flag = 'U' THEN
-- updating "end_date_active" allowed if:
-- (1) it terminates the relation, OR
-- (2) the current end_date_active is NULL
/*
-- (2) it does NOT revive a terminated relation AND
-- the resulted (start_date_active, end_date_active)
-- does not overlap with thoese of existing relations
*/
SELECT primary_flag
INTO l_primary_flag
FROM hz_code_assignments
WHERE code_assignment_id = p_in_rec.code_assignment_id;
FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
* FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
* FND_MESSAGE.SET_NAME('AR', 'HZ_API_NONUPDATEABLE_COLUMN');
UPDATE HZ_CODE_ASSIGNMENTS SET
end_date_active = SYSDATE
WHERE code_assignment_id = v_rec.code_assignment_id;
UPDATE HZ_CODE_ASSIGNMENTS SET
end_date_active = SYSDATE
WHERE code_assignment_id = v_rec.code_assignment_id;
UPDATE HZ_CODE_ASSIGNMENTS SET
end_date_active = SYSDATE
WHERE code_assignment_id = v_rec.code_assignment_id;
IF create_update_flag = 'C'
THEN
IF ((p_in_rec.owner_table_id IS NOT NULL AND
p_in_rec.owner_table_id <> FND_API.G_MISS_NUM) AND
(p_in_rec.owner_table_key_1 IS NOT NULL AND
p_in_rec.owner_table_key_1 <> FND_API.G_MISS_CHAR)) OR
((p_in_rec.owner_table_id IS NULL OR
p_in_rec.owner_table_id = FND_API.G_MISS_NUM) AND
(p_in_rec.owner_table_key_1 IS NULL OR
p_in_rec.owner_table_key_1 = FND_API.G_MISS_CHAR))
THEN
FND_MESSAGE.SET_NAME('AR', 'HZ_API_INVALID_COMBINATION2');
SELECT class_code,
start_date_active,
end_date_active
FROM hz_class_code_relations
WHERE class_category = p_class_category
AND sub_class_code = p_class_code
AND hz_class_validate_v2pub.is_overlap(start_date_active, end_date_active,
p_start_date_active, p_end_date_active)='Y'
AND ROWNUM = 1;
SELECT start_date_active,
end_date_active ,
sub_class_code
FROM hz_class_code_relations
WHERE class_category = p_class_category
AND class_code = p_class_code
AND ( NVL(end_date_active, p_start_date_active) >= p_start_date_active
OR start_date_active <= NVL(p_end_date_active, start_date_active) )
AND ROWNUM = 1 ;
SELECT allow_multi_parent_flag
FROM hz_class_categories
WHERE class_category = p_class_category;
select class_code,
sub_class_code,
start_date_active,
end_date_active
from hz_class_code_relations
where class_category = in_class_category
and sub_class_code = l_class_code
and (hz_class_validate_v2pub.is_overlap
(in_date_start, in_date_end,
start_date_active, end_date_active)= 'Y');
select class_code,
sub_class_code,
start_date_active,
end_date_active
from hz_class_code_relations
where class_category = in_class_category
and class_code = l_sub_class_code
and (hz_class_validate_v2pub.is_overlap
(in_date_start, in_date_end,
start_date_active, end_date_active)= 'Y');
select end_date_active
from hz_class_code_relations
where class_category = p_class_category
and class_code = p_class_code
and sub_class_code = p_sub_class_code
and start_date_active = p_start_date_active
and rownum = 1;
( p_create_update_flag varchar2,
p_class_category varchar2,
p_class_code varchar2,
p_sub_class_code varchar2,
p_start_date_active date,
p_end_date_active date,
x_start_date_active in out NOCOPY date,
x_end_date_active in out NOCOPY date )
Return varchar2
is
cursor c_create
is
select start_date_active,
end_date_active
from hz_class_code_relations
where class_category = p_class_category
and class_code = p_class_code
and sub_class_code = p_sub_class_code
and hz_class_validate_v2pub.is_overlap(start_date_active , end_date_active,
p_start_date_active, p_end_date_active )= 'Y';
cursor c_update
is
select start_date_active,
end_date_active
from hz_class_code_relations
where class_category = p_class_category
and class_code = p_class_code
and sub_class_code = p_sub_class_code
and start_date_active <> p_start_date_active
and hz_class_validate_v2pub.is_overlap(start_date_active , end_date_active,
p_start_date_active, p_end_date_active ) = 'Y';
if p_create_update_flag = 'C' then
open c_create;
elsif p_create_update_flag = 'U' then
open c_update;
fetch c_update into x_start_date_active, x_end_date_active;
if c_update%notfound then
result := 'N';
close c_update;
create_update_flag IN VARCHAR2,
x_return_status IN OUT NOCOPY VARCHAR2
) IS
l_end_date DATE := NULL;
SELECT created_by_module
FROM hz_class_code_relations
WHERE class_category = p_class_category
AND class_code = p_class_code
AND sub_class_code = p_sub_class_code;
IF create_update_flag = 'U' THEN
OPEN c_code_rel(
p_in_rec.class_category, p_in_rec.class_code, p_in_rec.sub_class_code);
create_update_flag,
'class_category',
p_in_rec.class_category,
FALSE,
FALSE, -- cannot be missing: part of PK
x_return_status);
create_update_flag,
'class_code',
p_in_rec.class_code,
FALSE,
FALSE, -- cannot be missing: part of PK
x_return_status);
create_update_flag,
'sub_class_code',
p_in_rec.sub_class_code,
FALSE,
FALSE, -- cannot be missing: part of PK
x_return_status);
create_update_flag,
'start_date_active',
p_in_rec.start_date_active,
FALSE,
FALSE, -- cannot be missing: part of PK
x_return_status);
p_create_update_flag => create_update_flag,
p_created_by_module => p_in_rec.created_by_module,
p_old_created_by_module => l_created_by_module,
x_return_status => x_return_status);
if create_update_flag = 'C' then
-- Check PK
if (exist_pk_relation( p_in_rec.class_category,
p_in_rec.class_code ,
p_in_rec.sub_class_code,
p_in_rec.start_date_active,
l_end_date ) = 'Y' ) then
fnd_message.set_name('AR', 'HZ_API_DUPLICATE_COLUMN');
if create_update_flag = 'U' then
if ( exist_pk_relation( p_in_rec.class_category,
p_in_rec.class_code ,
p_in_rec.sub_class_code,
p_in_rec.start_date_active,
l_end) = 'N') then
-- Relation does not exist
fnd_message.set_name('AR', 'HZ_API_REL_NOT_EXIST');
SELECT 'Y'
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = l_class_category_type
AND LOOKUP_CODE = l_class_category_code
AND SECURITY_GROUP_ID =l_security_group_id
AND VIEW_APPLICATION_ID = l_view_application_id
AND LANGUAGE = userenv('LANG')
AND ROWNUM = 1;
SELECT 'Y'
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = l_class_category_type
AND MEANING = l_class_category_meaning
AND SECURITY_GROUP_ID =l_security_group_id
AND VIEW_APPLICATION_ID = l_view_application_id
AND LANGUAGE = userenv('LANG')
AND ROWNUM = 1;