[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
create, update and delete resource group relations from other modules.
Its main procedures are as following:
Create Resource Group Relate
Update Resource Group Relate
Delete Resource Group Relate
All bUsiness logic validations are done through this API
******************************************************************************************/
G_PKG_NAME VARCHAR2(30) := 'JTF_RS_GROUP_RELATE_PVT';
SELECT 'X'
FROM jtf_rs_grp_relations rel
WHERE rel.group_id = l_group_id
AND NVL(rel.delete_flag,'N') <> 'Y'
AND rel.relation_type = 'PARENT_GROUP'
AND ((l_start_date_active between rel.start_date_active and
nvl(rel.end_date_active,l_start_date_active+1))
OR (l_end_date_active between rel.start_date_active
and nvl(rel.end_date_active,l_end_date_active))
OR ((l_start_date_active <= rel.start_date_active)
AND (l_end_date_active >= rel.end_date_active
OR l_end_date_active IS NULL )));
SELECT 'X'
FROM jtf_rs_groups_b grp
WHERE grp.group_id = l_group_id
-- changed by nsinghai 20 May 2002 to handle null value of l_end_date_active
-- AND l_start_date_active >= grp.start_date_active
-- AND ((grp.end_date_active IS NULL)
-- OR (grp.end_date_active >= nvl(l_end_date_active,grp.end_date_active)));
SELECT 'X'
FROM jtf_rs_groups_b g1
,jtf_rs_groups_b g2
WHERE g1.group_id = l_group_id
AND g2.group_id = l_related_group_id
AND ((g1.start_date_active <= g2.end_date_active and g2.end_date_active <> NULL)
or ((g2.end_date_active IS NULL) AND (g1.end_date_active <> NULL) AND
(g1.end_date_active >= g2.start_date_active))
OR((g2.end_date_active IS NULL) AND (g1.end_date_active IS NULL)));
select 'x'
from jtf_rs_groups_denorm
where parent_group_id = l_group_id
and group_id = l_related_group_id
and ( ( (l_start_date_active >= start_date_active)
AND ((l_start_date_active <= end_date_active)
OR (end_date_active IS NULL))
)
OR (
(l_end_date_active between start_date_active and nvl(end_date_active,l_g_miss_date))
OR ((nvl(l_end_date_active,start_date_active) >= start_date_active)
AND (end_date_active IS NULL))
-- OR (nvl(l_end_date_active,sysdate) <= end_date_active)
)
OR (
(l_start_date_active <= start_date_active)
AND
(nvl(l_end_date_active,l_g_miss_date) >= nvl(end_date_active,l_g_miss_date))
)
);
SELECT jtf_rs_grp_relations_s.nextval
FROM dual;
SELECT count(*) par_count
from jtf_rs_grp_relations rel
where rel.relation_type = 'PARENT_GROUP'
connect by rel.group_id = prior related_group_id
and nvl(delete_flag, 'N') <> 'Y'
and rel.related_group_id <> l_group_id
AND ((trunc(rel.start_date_active) <= prior rel.start_date_active
AND nvl(rel.end_date_active, prior rel.start_date_active) >=
trunc(prior rel.start_date_active)) OR
(rel.start_date_active > trunc(prior rel.start_date_active)
AND trunc(rel.start_date_active) <= nvl(prior rel.end_date_active,
rel.start_date_active)))
start with rel.group_id = l_group_id
and nvl(rel.delete_flag,'N') <> 'Y';
SELECT count(*) par_count
from jtf_rs_grp_relations rel
where rel.relation_type = 'PARENT_GROUP'
connect by rel.related_group_id = prior group_id
and nvl(delete_flag, 'N') <> 'Y'
and rel.group_id <> l_group_id
AND ((trunc(rel.start_date_active) <= prior rel.start_date_active
AND nvl(rel.end_date_active, prior rel.start_date_active) >=
trunc(prior rel.start_date_active)) OR
(rel.start_date_active > trunc(prior rel.start_date_active)
AND trunc(rel.start_date_active) <= nvl(prior rel.end_date_active,
rel.start_date_active)))
start with rel.related_group_id = l_group_id
and nvl(rel.delete_flag,'N') <> 'Y';
select description
from fnd_concurrent_programs_vl
where concurrent_program_name = 'JTFRSDEN'
and application_id = 690;
JTF_RS_GROUP_RELATE_AUD_PVT.insert_group_relate(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => p_init_msg_list,
P_COMMIT => null,
P_GROUP_RELATE_ID => l_group_relate_id,
P_GROUP_ID => l_group_id,
P_RELATED_GROUP_ID => l_related_group_id,
P_RELATION_TYPE => l_relation_type,
P_START_DATE_ACTIVE => l_start_date_active,
P_END_DATE_ACTIVE => l_end_date_active,
P_OBJECT_VERSION_NUMBER => 1.0,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
jtf_rs_grp_relations_pkg.insert_row(
X_ROWID => l_rowid,
X_GROUP_RELATE_ID => l_group_relate_id,
X_GROUP_ID => l_group_id,
X_RELATED_GROUP_ID => l_related_group_id,
X_RELATION_TYPE => l_relation_type,
X_START_DATE_ACTIVE => l_start_date_active,
X_END_DATE_ACTIVE => l_end_date_active,
X_DELETE_FLAG => 'N',
X_ATTRIBUTE2 => p_attribute2,
X_ATTRIBUTE3 => p_attribute3,
X_ATTRIBUTE4 => p_attribute4,
X_ATTRIBUTE5 => p_attribute5,
X_ATTRIBUTE6 => p_attribute6,
X_ATTRIBUTE7 => p_attribute7,
X_ATTRIBUTE8 => p_attribute8,
X_ATTRIBUTE9 => p_attribute9,
X_ATTRIBUTE10 => p_attribute10,
X_ATTRIBUTE11 => p_attribute11,
X_ATTRIBUTE12 => p_attribute12,
X_ATTRIBUTE13 => p_attribute13,
X_ATTRIBUTE14 => p_attribute14,
X_ATTRIBUTE15 => p_attribute15,
X_ATTRIBUTE_CATEGORY => p_attribute_category,
X_ATTRIBUTE1 => p_attribute1,
X_CREATION_DATE => l_date,
X_CREATED_BY => l_user_id,
X_LAST_UPDATE_DATE => l_date,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => l_login_id); --call to insert records in jtf_rs_groups_denorm
insert into jtf_rs_chgd_grp_relations
(GROUP_RELATE_ID,
GROUP_ID ,
RELATED_GROUP_ID,
RELATION_TYPE ,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
OPERATION_FLAG,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
values(l_group_relate_id,
p_group_id,
p_related_group_id,
p_relation_type,
p_start_date_active,
p_end_date_active,
'I',
l_user_id,
l_date,
l_user_id,
l_date,
l_login_id);
JTF_RS_GROUP_DENORM_PVT.INSERT_GROUPS
( P_API_VERSION => 1.0,
P_INIT_MSG_LIST => p_init_msg_list,
P_COMMIT => null,
P_GROUP_ID => l_group_id,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
/* JTF_RS_REP_MGR_DENORM_PVT.INSERT_GRP_RELATIONS
( P_API_VERSION => 1.0,
P_INIT_MSG_LIST => p_init_msg_list,
P_COMMIT => null,
P_GROUP_RELATE_ID => l_group_relate_id,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
/* Procedure to update the resource group relation
based on input values passed by calling routines. */
PROCEDURE update_resource_group_relate
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_GROUP_RELATE_ID IN JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID%TYPE,
P_START_DATE_ACTIVE IN JTF_RS_GRP_RELATIONS.START_DATE_ACTIVE%TYPE,
P_END_DATE_ACTIVE IN JTF_RS_GRP_RELATIONS.END_DATE_ACTIVE%TYPE,
P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_GROUPS_VL.OBJECT_VERSION_NUMBER%TYPE,
P_ATTRIBUTE1 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE1%TYPE,
P_ATTRIBUTE2 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE2%TYPE,
P_ATTRIBUTE3 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE3%TYPE,
P_ATTRIBUTE4 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE4%TYPE,
P_ATTRIBUTE5 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE5%TYPE,
P_ATTRIBUTE6 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE6%TYPE,
P_ATTRIBUTE7 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE7%TYPE,
P_ATTRIBUTE8 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE8%TYPE,
P_ATTRIBUTE9 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE9%TYPE,
P_ATTRIBUTE10 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE10%TYPE,
P_ATTRIBUTE11 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE11%TYPE,
P_ATTRIBUTE12 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE12%TYPE,
P_ATTRIBUTE13 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE13%TYPE,
P_ATTRIBUTE14 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE14%TYPE,
P_ATTRIBUTE15 IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE15%TYPE,
P_ATTRIBUTE_CATEGORY IN JTF_RS_ROLE_RELATIONS.ATTRIBUTE_CATEGORY%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE_GROUP_RELATE';
l_delete_flag JTF_RS_GRP_RELATIONS.DELETE_FLAG%TYPE;
SELECT group_id,
related_group_id,
start_date_active,
end_date_active,
relation_type,
object_version_number,
delete_flag,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category
FROM jtf_rs_grp_relations
WHERE group_relate_id = l_group_relate_id;
SELECT 'X'
FROM jtf_rs_grp_relations rel
WHERE rel.group_relate_id <> l_group_relate_id
AND rel.group_id = l_group_id
AND NVL(rel.delete_flag,'N') <> 'Y'
AND rel.relation_type = 'PARENT_GROUP'
AND rel.group_relate_id <> p_group_relate_id
AND ((l_start_date_active between rel.start_date_active and
nvl(rel.end_date_active,l_start_date_active+1))
OR (l_end_date_active between rel.start_date_active
and nvl(rel.end_date_active,l_end_date_active))
OR ((l_start_date_active <= rel.start_date_active)
AND (l_end_date_active >= rel.end_date_active
OR l_end_date_active IS NULL )));
SELECT 'X'
FROM jtf_rs_groups_b grp
WHERE grp.group_id = l_group_id
-- changed by nsinghai 20 May 2002 to handle null value of l_end_date_active
-- AND l_start_date_active >= grp.start_date_active
-- AND ((grp.end_date_active IS NULL)
-- OR (grp.end_date_active >= nvl(l_end_date_active,grp.end_date_active)));
SELECT 'X'
FROM jtf_rs_groups_b g1
,jtf_rs_groups_b g2
WHERE g1.group_id = l_group_id
AND g2.group_id = l_related_group_id
AND ((g1.start_date_active <= g2.end_date_active and g2.end_date_active <> NULL)
or ((g2.end_date_active IS NULL) AND (g1.end_date_active <> NULL) AND
(g1.end_date_active >= g2.start_date_active))
OR((g2.end_date_active IS NULL) AND (g1.end_date_active IS NULL)));
select 'x'
from jtf_rs_groups_denorm
where parent_group_id = l_group_id
and group_id = l_related_group_id
and ( ( (l_start_date_active >= start_date_active)
AND ((l_start_date_active <= end_date_active)
OR (end_date_active IS NULL))
)
OR (
(l_end_date_active between start_date_active and nvl(end_date_active,l_g_miss_date))
OR ((nvl(l_end_date_active,start_date_active) >= start_date_active)
AND (end_date_active IS NULL))
-- OR (nvl(l_end_date_active,sysdate) <= end_date_active)
)
OR (
(l_start_date_active <= start_date_active)
AND
(nvl(l_end_date_active,l_g_miss_date) >= nvl(end_date_active,l_g_miss_date))
)
);
SELECT count(*) par_count
from jtf_rs_grp_relations rel
where rel.relation_type = 'PARENT_GROUP'
connect by rel.group_id = prior related_group_id
and nvl(delete_flag, 'N') <> 'Y'
and rel.related_group_id <> l_group_id
AND ((trunc(rel.start_date_active) <= prior rel.start_date_active
AND nvl(rel.end_date_active, prior rel.start_date_active) >=
trunc(prior rel.start_date_active)) OR
(rel.start_date_active > trunc(prior rel.start_date_active)
AND trunc(rel.start_date_active) <= nvl(prior rel.end_date_active,
rel.start_date_active)))
start with rel.group_id = l_group_id
and nvl(rel.delete_flag,'N') <> 'Y';
SELECT count(*) par_count
from jtf_rs_grp_relations rel
where rel.relation_type = 'PARENT_GROUP'
connect by rel.related_group_id = prior group_id
and nvl(delete_flag, 'N') <> 'Y'
and rel.group_id <> l_group_id
AND ((trunc(rel.start_date_active) <= prior rel.start_date_active
AND nvl(rel.end_date_active, prior rel.start_date_active) >=
trunc(prior rel.start_date_active)) OR
(rel.start_date_active > trunc(prior rel.start_date_active)
AND trunc(rel.start_date_active) <= nvl(prior rel.end_date_active,
rel.start_date_active)))
start with rel.related_group_id = l_group_id
and nvl(rel.delete_flag,'N') <> 'Y';
select description
from fnd_concurrent_programs_vl
where concurrent_program_name = 'JTFRSDEN'
and application_id = 690;
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'UPDATE_RESOURCE_GROUP_RELATE', 'B', 'C' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'UPDATE_RESOURCE_GROUP_RELATE', 'B', 'C' ))
then
JTF_RS_GROUP_RELATE_CUHK.UPDATE_RES_GROUP_RELATE_PRE(P_GROUP_RELATE_ID => p_group_relate_id,
P_START_DATE_ACTIVE => p_start_date_active,
P_END_DATE_ACTIVE => p_end_date_active,
P_OBJECT_VERSION_NUM => p_object_version_num,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'UPDATE_RESOURCE_GROUP_RELATE', 'B', 'V' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'UPDATE_RESOURCE_GROUP_RELATE', 'B', 'V' ))
then
JTF_RS_GROUP_RELATE_VUHK.UPDATE_RES_GROUP_RELATE_PRE(P_GROUP_RELATE_ID => p_group_relate_id,
P_START_DATE_ACTIVE => p_start_date_active,
P_END_DATE_ACTIVE => p_end_date_active,
P_OBJECT_VERSION_NUM => p_object_version_num,
p_data => l_data,
p_count => l_count,
P_return_code => l_return_code);
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'UPDATE_RESOURCE_GROUP_RELATE', 'B', 'I' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'UPDATE_RESOURCE_GROUP_RELATE', 'B', 'I' ))
then
JTF_RS_GROUP_RELATE_IUHK.UPDATE_RES_GROUP_RELATE_PRE(P_GROUP_RELATE_ID => p_group_relate_id,
P_START_DATE_ACTIVE => p_start_date_active,
P_END_DATE_ACTIVE => p_end_date_active,
P_OBJECT_VERSION_NUM => p_object_version_num,
p_data => l_data,
p_count => l_count,
P_return_code => l_return_code);
l_delete_flag := grp_rel_rec.delete_flag;
JTF_RS_GROUP_RELATE_AUD_PVT.update_group_relate(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => p_init_msg_list,
P_COMMIT => null,
P_GROUP_RELATE_ID => l_group_relate_id,
P_GROUP_ID => l_group_id,
P_RELATED_GROUP_ID => l_related_group_id,
P_RELATION_TYPE => l_relation_type,
P_START_DATE_ACTIVE => l_start_date_active,
P_END_DATE_ACTIVE => l_end_date_active,
P_OBJECT_VERSION_NUMBER => l_object_version_number,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
jtf_rs_grp_relations_pkg.update_row(
X_GROUP_RELATE_ID => l_group_relate_id,
X_GROUP_ID => l_group_id,
X_RELATED_GROUP_ID => l_related_group_id,
X_RELATION_TYPE => l_relation_type,
X_START_DATE_ACTIVE => l_start_date_active,
X_END_DATE_ACTIVE => l_end_date_active,
X_DELETE_FLAG => l_delete_flag,
X_OBJECT_VERSION_NUMBER => l_object_version_number,
X_ATTRIBUTE2 => l_attribute2,
X_ATTRIBUTE3 => l_attribute3,
X_ATTRIBUTE4 => l_attribute4,
X_ATTRIBUTE5 => l_attribute5,
X_ATTRIBUTE6 => l_attribute6,
X_ATTRIBUTE7 => l_attribute7,
X_ATTRIBUTE8 => l_attribute8,
X_ATTRIBUTE9 => l_attribute9,
X_ATTRIBUTE10 => l_attribute10,
X_ATTRIBUTE11 => l_attribute11,
X_ATTRIBUTE12 => l_attribute12,
X_ATTRIBUTE13 => l_attribute13,
X_ATTRIBUTE14 => l_attribute14,
X_ATTRIBUTE15 => l_attribute15,
X_ATTRIBUTE_CATEGORY => l_attribute_category,
X_ATTRIBUTE1 => l_attribute1,
X_LAST_UPDATE_DATE => l_date,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => l_login_id);
insert into jtf_rs_chgd_grp_relations
(GROUP_RELATE_ID,
GROUP_ID ,
RELATED_GROUP_ID,
RELATION_TYPE ,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
OPERATION_FLAG,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
values(p_group_relate_id,
l_group_id,
l_related_group_id,
l_relation_type,
l_start_date_active,
l_end_date_active,
'U',
l_user_id,
l_date,
l_user_id,
l_date,
l_login_id);
JTF_RS_GROUP_DENORM_PVT.UPDATE_GROUPS
( P_API_VERSION => 1.0,
P_INIT_MSG_LIST => p_init_msg_list,
P_COMMIT => null,
P_GROUP_ID => l_group_id,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'UPDATE_RESOURCE_GROUP_RELATE', 'A', 'C' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'UPDATE_RESOURCE_GROUP_RELATE', 'A', 'C' ))
then
JTF_RS_GROUP_RELATE_CUHK.UPDATE_RES_GROUP_RELATE_POST(P_GROUP_RELATE_ID => p_group_relate_id,
P_START_DATE_ACTIVE => p_start_date_active,
P_END_DATE_ACTIVE => p_end_date_active,
P_OBJECT_VERSION_NUM => p_object_version_num,
p_data => L_data,
p_count => l_count,
P_return_code => l_return_code);
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'UPDATE_RESOURCE_GROUP_RELATE', 'A', 'V' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'UPDATE_RESOURCE_GROUP_RELATE', 'A', 'V' ))
then
JTF_RS_GROUP_RELATE_VUHK.UPDATE_RES_GROUP_RELATE_POST(P_GROUP_RELATE_ID => p_group_relate_id,
P_START_DATE_ACTIVE => p_start_date_active,
P_END_DATE_ACTIVE => p_end_date_active,
P_OBJECT_VERSION_NUM => p_object_version_num,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'UPDATE_RESOURCE_GROUP_RELATE', 'A', 'I' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'UPDATE_RESOURCE_GROUP_RELATE', 'A', 'I' ))
then
JTF_RS_GROUP_RELATE_IUHK.UPDATE_RES_GROUP_RELATE_POST(P_GROUP_RELATE_ID => p_group_relate_id,
P_START_DATE_ACTIVE => p_start_date_active,
P_END_DATE_ACTIVE => p_end_date_active,
P_OBJECT_VERSION_NUM => p_object_version_num,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
'UPDATE_RESOURCE_GROUP_RELATE',
'M',
'M')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_GROUP_RELATE_PVT',
'UPDATE_RESOURCE_GROUP_RELATE',
'M',
'M')
THEN
IF (jtf_rs_group_relate_cuhk.ok_to_generate_msg(
p_group_relate_id => l_group_relate_id,
x_return_status => x_return_status) )
THEN
/* Get the bind data id for the Business Object Instance */
l_bind_data_id := jtf_usr_hks.get_bind_data_id;
END update_resource_group_relate;
/* Procedure to delete the resource group relation. */
PROCEDURE delete_resource_group_relate
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_GROUP_RELATE_ID IN JTF_RS_GRP_RELATIONS.GROUP_RELATE_ID%TYPE,
P_OBJECT_VERSION_NUM IN JTF_RS_GROUPS_VL.OBJECT_VERSION_NUMBER%TYPE,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_RESOURCE_GROUP_RELATE';
SELECT group_id,
related_group_id,
start_date_active,
end_date_active,
relation_type,
object_version_number,
delete_flag,
attribute1,
attribute2,
attribute3,
attribute4,
attribute5,
attribute6,
attribute7,
attribute8,
attribute9,
attribute10,
attribute11,
attribute12,
attribute13,
attribute14,
attribute15,
attribute_category
FROM jtf_rs_grp_relations
WHERE group_relate_id = l_group_relate_id;
SELECT count(*) par_count
from jtf_rs_grp_relations rel
where rel.relation_type = 'PARENT_GROUP'
connect by rel.group_id = prior related_group_id
and nvl(delete_flag, 'N') <> 'Y'
and rel.related_group_id <> l_group_id
AND ((trunc(rel.start_date_active) <= prior rel.start_date_active
AND nvl(rel.end_date_active, prior rel.start_date_active) >=
trunc(prior rel.start_date_active)) OR
(rel.start_date_active > trunc(prior rel.start_date_active)
AND trunc(rel.start_date_active) <= nvl(prior rel.end_date_active,
rel.start_date_active)))
start with rel.group_id = l_group_id
and nvl(rel.delete_flag,'N') <> 'Y';
SELECT count(*) child_count
from jtf_rs_grp_relations rel
where rel.relation_type = 'PARENT_GROUP'
connect by rel.related_group_id = prior group_id
and nvl(delete_flag, 'N') <> 'Y'
and rel.group_id <> l_group_id
AND ((trunc(rel.start_date_active) <= prior rel.start_date_active
AND nvl(rel.end_date_active, prior rel.start_date_active) >=
trunc(prior rel.start_date_active)) OR
(rel.start_date_active > trunc(prior rel.start_date_active)
AND trunc(rel.start_date_active) <= nvl(prior rel.end_date_active,
rel.start_date_active)))
start with rel.related_group_id = l_group_id
and nvl(rel.delete_flag,'N') <> 'Y';
select description
from fnd_concurrent_programs_vl
where concurrent_program_name = 'JTFRSDEN'
and application_id = 690;
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'DELETE_RESOURCE_GROUP_RELATE', 'B', 'C' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'DELETE_RESOURCE_GROUP_RELATE', 'B', 'C' ))
then
JTF_RS_GROUP_RELATE_CUHK.DELETE_RES_GROUP_RELATE_PRE(P_GROUP_RELATE_ID => p_group_relate_id,
P_OBJECT_VERSION_NUM => p_object_version_num,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'DELETE_RESOURCE_GROUP_RELATE', 'B', 'V' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'DELETE_RESOURCE_GROUP_RELATE', 'B', 'V' ))
then
JTF_RS_GROUP_RELATE_VUHK.DELETE_RES_GROUP_RELATE_PRE(P_GROUP_RELATE_ID => p_group_relate_id,
P_OBJECT_VERSION_NUM => p_object_version_num,
p_data => l_data,
p_count => l_count,
P_return_code => l_return_code);
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'DELETE_RESOURCE_GROUP_RELATE', 'B', 'I' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'DELETE_RESOURCE_GROUP_RELATE', 'B', 'I' ))
then
JTF_RS_GROUP_RELATE_IUHK.DELETE_RES_GROUP_RELATE_PRE(P_GROUP_RELATE_ID => p_group_relate_id,
P_OBJECT_VERSION_NUM => p_object_version_num,
p_data => l_data,
p_count => l_count,
P_return_code => l_return_code);
JTF_RS_GROUP_RELATE_AUD_PVT.delete_group_relate(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => p_init_msg_list,
P_COMMIT => null,
P_GROUP_RELATE_ID => p_group_relate_id,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
jtf_rs_grp_relations_pkg.update_row(
X_GROUP_RELATE_ID => p_group_relate_id,
X_GROUP_ID => grp_rel_rec.group_id,
X_RELATED_GROUP_ID => grp_rel_rec.related_group_id,
X_RELATION_TYPE => grp_rel_rec.relation_type,
X_START_DATE_ACTIVE => grp_rel_rec.start_date_active,
X_END_DATE_ACTIVE => grp_rel_rec.end_date_active,
X_DELETE_FLAG => 'Y',
X_OBJECT_VERSION_NUMBER => grp_rel_rec.object_version_number,
X_ATTRIBUTE2 => grp_rel_rec.attribute2,
X_ATTRIBUTE3 => grp_rel_rec.attribute3,
X_ATTRIBUTE4 => grp_rel_rec.attribute4,
X_ATTRIBUTE5 => grp_rel_rec.attribute5,
X_ATTRIBUTE6 => grp_rel_rec.attribute6,
X_ATTRIBUTE7 => grp_rel_rec.attribute7,
X_ATTRIBUTE8 => grp_rel_rec.attribute8,
X_ATTRIBUTE9 => grp_rel_rec.attribute9,
X_ATTRIBUTE10 => grp_rel_rec.attribute10,
X_ATTRIBUTE11 => grp_rel_rec.attribute11,
X_ATTRIBUTE12 => grp_rel_rec.attribute12,
X_ATTRIBUTE13 => grp_rel_rec.attribute13,
X_ATTRIBUTE14 => grp_rel_rec.attribute14,
X_ATTRIBUTE15 => grp_rel_rec.attribute15,
X_ATTRIBUTE_CATEGORY => grp_rel_rec.attribute_category,
X_ATTRIBUTE1 => grp_rel_rec.attribute1,
X_LAST_UPDATE_DATE => l_date,
X_LAST_UPDATED_BY => l_user_id,
X_LAST_UPDATE_LOGIN => l_login_id);
insert into jtf_rs_chgd_grp_relations
(GROUP_RELATE_ID,
GROUP_ID ,
RELATED_GROUP_ID,
RELATION_TYPE ,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
OPERATION_FLAG,
CREATED_BY ,
CREATION_DATE ,
LAST_UPDATED_BY ,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN)
values(p_group_relate_id,
grp_rel_rec.group_id,
grp_rel_rec.related_group_id,
grp_rel_rec.relation_type,
grp_rel_rec.start_date_active,
grp_rel_rec.end_date_active,
'D',
l_user_id,
l_date,
l_user_id,
l_date,
l_login_id);
/* JTF_RS_GROUP_DENORM_PVT.DELETE_GROUPS
( P_API_VERSION => 1.0,
P_INIT_MSG_LIST => p_init_msg_list,
P_COMMIT => null,
P_GROUP_ID => grp_rel_rec.group_id,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data); */
JTF_RS_GROUP_DENORM_PVT.DELETE_GRP_RELATIONS
( P_API_VERSION => 1.0,
P_INIT_MSG_LIST => p_init_msg_list,
P_COMMIT => null,
P_group_relate_id => p_group_relate_id,
P_GROUP_ID => grp_rel_rec.group_id,
P_RELATED_GROUP_ID => grp_rel_rec.related_group_id,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'DELETE_RESOURCE_GROUP_RELATE', 'A', 'C' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'DELETE_RESOURCE_GROUP_RELATE', 'A', 'C' ))
then
JTF_RS_GROUP_RELATE_CUHK.DELETE_RES_GROUP_RELATE_POST(P_GROUP_RELATE_ID => p_group_relate_id,
P_OBJECT_VERSION_NUM => p_object_version_num,
p_data => L_data,
p_count => l_count,
P_return_code => l_return_code);
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'DELETE_RESOURCE_GROUP_RELATE', 'A', 'V' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'DELETE_RESOURCE_GROUP_RELATE', 'A', 'V' ))
then
JTF_RS_GROUP_RELATE_VUHK.DELETE_RES_GROUP_RELATE_POST(P_GROUP_RELATE_ID => p_group_relate_id,
P_OBJECT_VERSION_NUM => p_object_version_num,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
if ( JTF_RESOURCE_UTL.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'DELETE_RESOURCE_GROUP_RELATE', 'A', 'I' ))
then
if ( JTF_USR_HKS.Ok_to_execute( 'JTF_RS_GROUP_RELATE_PVT', 'DELETE_RESOURCE_GROUP_RELATE', 'A', 'I' ))
then
JTF_RS_GROUP_RELATE_IUHK.DELETE_RES_GROUP_RELATE_POST(P_GROUP_RELATE_ID => p_group_relate_id,
P_OBJECT_VERSION_NUM => p_object_version_num,
p_data => L_data,
p_count => L_count,
P_return_code => l_return_code);
'DELETE_RESOURCE_GROUP_RELATE',
'M',
'M')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_GROUP_RELATE_PVT',
'DELETE_RESOURCE_GROUP_RELATE',
'M',
'M')
THEN
IF (jtf_rs_group_relate_cuhk.ok_to_generate_msg(
p_group_relate_id => p_group_relate_id,
x_return_status => x_return_status) )
THEN
/* Get the bind data id for the Business Object Instance */
l_bind_data_id := jtf_usr_hks.get_bind_data_id;
END delete_resource_group_relate;