[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
like create and delete resource group usages.
Its main procedures are as following:
Create Resource Group Usage
Delete Resource Group Usage
These procedures does the business validations and then Calls the corresponding
table handlers to do actual inserts and deletes into tables.
******************************************************************************************/
/* Package variables. */
G_PKG_NAME VARCHAR2(30) := 'JTF_RS_GROUP_USAGES_PVT';
SELECT 'Y'
FROM jtf_rs_groups_vl G1,
jtf_rs_groups_vl G2,
jtf_rs_group_members GM1,
jtf_rs_group_members GM2,
jtf_rs_group_usages GU2,
jtf_rs_role_relations RR1,
jtf_rs_role_relations RR2
WHERE G1.group_id = GM1.group_id
AND G2.group_id = GM2.group_id
AND nvl(GM1.delete_flag, 'N') <> 'Y'
AND nvl(GM2.delete_flag, 'N') <> 'Y'
AND GM1.resource_id = GM2.resource_id
AND GM1.group_member_id = RR1.role_resource_id
AND GM2.group_member_id = RR2.role_resource_id
AND RR1.role_resource_type = 'RS_GROUP_MEMBER'
AND RR2.role_resource_type = 'RS_GROUP_MEMBER'
AND nvl(RR1.delete_flag, 'N') <> 'Y'
AND nvl(RR2.delete_flag, 'N') <> 'Y'
/*AND NOT (((RR2.end_date_active < RR1.start_date_active OR
RR2.start_date_active > RR1.end_date_active) AND
RR1.end_date_active IS NOT NULL)
OR (RR2.end_date_active < RR1.start_date_active AND
RR1.end_date_active IS NULL)) */
AND not (((nvl(RR2.end_date_active,RR1.start_date_active + 1) < RR1.start_date_active OR
RR2.start_date_active > RR1.end_date_active) AND
RR1.end_date_active IS NOT NULL)
OR ( nvl(RR2.end_date_active,RR1.start_date_active + 1) < RR1.start_date_active AND
RR1.end_date_active IS NULL ))
AND G2.exclusive_flag = 'Y'
AND GU2.group_id = G2.group_id
AND GU2.usage = l_usage
AND G1.group_id <> G2.group_id
AND G1.group_id = l_group_id
AND nvl(G1.exclusive_flag,'N') = 'Y';
SELECT 'Y'
FROM jtf_rs_group_usages
WHERE ROWID = l_rowid;
SELECT count(*)
INTO l_check_count
FROM jtf_rs_group_usages
WHERE group_id = l_group_id
AND usage = l_usage;
SELECT jtf_rs_group_usages_s.nextval
INTO l_group_usage_id
FROM dual;
/* Insert the row into the table by calling the table handler. */
jtf_rs_group_usages_pkg.insert_row(
x_rowid => l_rowid,
x_group_usage_id => l_group_usage_id,
x_group_id => l_group_id,
x_usage => l_usage,
x_attribute1 => p_attribute1,
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_creation_date => SYSDATE,
x_created_by => jtf_resource_utl.created_by,
x_last_update_date => SYSDATE,
x_last_updated_by => jtf_resource_utl.updated_by,
x_last_update_login => jtf_resource_utl.login_id
);
/* Procedure to delete the resource group usage
based on input values passed by calling routines. */
PROCEDURE delete_group_usage
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_GROUP_ID IN JTF_RS_GROUP_USAGES.GROUP_ID%TYPE,
P_USAGE IN JTF_RS_GROUP_USAGES.USAGE%TYPE,
P_OBJECT_VERSION_NUM IN JTF_RS_GROUP_USAGES.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_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_GROUP_USAGE';
SELECT group_usage_id
FROM jtf_rs_group_usages
WHERE group_id = l_group_id
AND usage = l_usage;
select meaning
from fnd_lookups
where lookup_type = 'JTF_RS_USAGE'
and lookup_code = c_usage_code;
SAVEPOINT delete_group_usage_pvt;
'DELETE_GROUP_USAGE',
'B',
'C')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_GROUP_USAGES_PVT',
'DELETE_GROUP_USAGE',
'B',
'C')
THEN
jtf_rs_group_usage_cuhk.delete_group_usage_pre(
p_group_id => l_group_id,
p_usage => l_usage,
x_return_status => x_return_status);
'DELETE_GROUP_USAGE',
'B',
'V')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_GROUP_USAGES_PVT',
'DELETE_GROUP_USAGE',
'B',
'V')
THEN
jtf_rs_group_usage_vuhk.delete_group_usage_pre(
p_group_id => l_group_id,
p_usage => l_usage,
x_return_status => x_return_status);
'DELETE_GROUP_USAGE',
'B',
'I')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_GROUP_USAGES_PVT',
'DELETE_GROUP_USAGE',
'B',
'I')
THEN
jtf_rs_group_usage_iuhk.delete_group_usage_pre(
p_group_id => l_group_id,
p_usage => l_usage,
x_return_status => x_return_status);
/* Call the private procedure for physical delete */
BEGIN
/* Delete the row into the table by calling the table handler. */
jtf_rs_group_usages_pkg.delete_row(
x_group_usage_id => l_group_usage_id
);
'DELETE_GROUP_USAGE',
'A',
'C')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_GROUP_USAGES_PVT',
'DELETE_GROUP_USAGE',
'A',
'C')
THEN
jtf_rs_group_usage_cuhk.delete_group_usage_post(
p_group_id => l_group_id,
p_usage => l_usage,
x_return_status => x_return_status);
'DELETE_GROUP_USAGE',
'A',
'V')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_GROUP_USAGES_PVT',
'DELETE_GROUP_USAGE',
'A',
'V')
THEN
jtf_rs_group_usage_vuhk.delete_group_usage_post(
p_group_id => l_group_id,
p_usage => l_usage,
x_return_status => x_return_status);
'DELETE_GROUP_USAGE',
'A',
'I')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_GROUP_USAGES_PVT',
'DELETE_GROUP_USAGE',
'A',
'I')
THEN
jtf_rs_group_usage_iuhk.delete_group_usage_post(
p_group_id => l_group_id,
p_usage => l_usage,
x_return_status => x_return_status);
'DELETE_GROUP_USAGE',
'M',
'M')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_GROUP_USAGES_PVT',
'DELETE_GROUP_USAGE',
'M',
'M')
THEN
IF (jtf_rs_group_usage_cuhk.ok_to_generate_msg(
p_group_usage_id => l_group_usage_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;
ROLLBACK TO delete_group_usage_pvt;
ROLLBACK TO delete_group_usage_pvt;
ROLLBACK TO delete_group_usage_pvt;
END delete_group_usage;