[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Update Resource Group Members
These procedures do the business validations and then call the appropriate
table handlers to do the actual inserts and updates.
******************************************************************************************/
/* Package variables. */
G_PKG_NAME VARCHAR2(30) := 'JTF_RS_GROUPS_PVT';
SELECT 'Y'
FROM jtf_rs_groups_b
WHERE ROWID = l_rowid;
SELECT 'X'
FROM jtf_rs_groups_vl
WHERE group_id = l_group_id;
SELECT jtf_rs_groups_s.nextval
INTO l_group_id
FROM dual;
SELECT jtf_rs_group_number_s.nextval
INTO l_group_number
FROM dual;
jtf_rs_groups_aud_pvt.insert_group
(P_API_VERSION => 1,
P_INIT_MSG_LIST => fnd_api.g_false,
P_COMMIT => fnd_api.g_false,
P_GROUP_ID => l_group_id,
P_GROUP_NUMBER => l_group_number,
P_GROUP_NAME => l_group_name,
P_GROUP_DESC => l_group_desc,
P_EXCLUSIVE_FLAG => l_exclusive_flag,
P_EMAIL_ADDRESS => l_email_address,
P_START_DATE_ACTIVE => l_start_date_active,
P_END_DATE_ACTIVE => l_end_date_active,
P_ACCOUNTING_CODE => l_accounting_code,
P_OBJECT_VERSION_NUMBER => 1,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data,
P_TIME_ZONE => l_time_zone
);
/* Insert the row into the table by calling the table handler. */
jtf_rs_groups_pkg.insert_row(
x_rowid => l_rowid,
x_group_id => l_group_id,
x_group_number => l_group_number,
x_exclusive_flag => l_exclusive_flag,
x_email_address => l_email_address,
x_start_date_active => l_start_date_active,
x_end_date_active => l_end_date_active,
x_group_name => l_group_name,
x_group_desc => l_group_desc,
x_accounting_code => l_accounting_code,
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,
x_time_zone => l_time_zone
);
/* Procedure to update the resource group based on input values
passed by calling routines. */
PROCEDURE update_resource_group
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_GROUP_ID IN JTF_RS_GROUPS_VL.GROUP_ID%TYPE,
P_GROUP_NAME IN JTF_RS_GROUPS_VL.GROUP_NAME%TYPE,
P_GROUP_DESC IN JTF_RS_GROUPS_VL.GROUP_DESC%TYPE,
P_EXCLUSIVE_FLAG IN JTF_RS_GROUPS_VL.EXCLUSIVE_FLAG%TYPE,
P_EMAIL_ADDRESS IN JTF_RS_GROUPS_VL.EMAIL_ADDRESS%TYPE,
P_START_DATE_ACTIVE IN JTF_RS_GROUPS_VL.START_DATE_ACTIVE%TYPE,
P_END_DATE_ACTIVE IN JTF_RS_GROUPS_VL.END_DATE_ACTIVE%TYPE,
P_ACCOUNTING_CODE IN JTF_RS_GROUPS_VL.ACCOUNTING_CODE%TYPE,
P_ATTRIBUTE1 IN JTF_RS_GROUPS_VL.ATTRIBUTE1%TYPE,
P_ATTRIBUTE2 IN JTF_RS_GROUPS_VL.ATTRIBUTE2%TYPE,
P_ATTRIBUTE3 IN JTF_RS_GROUPS_VL.ATTRIBUTE3%TYPE,
P_ATTRIBUTE4 IN JTF_RS_GROUPS_VL.ATTRIBUTE4%TYPE,
P_ATTRIBUTE5 IN JTF_RS_GROUPS_VL.ATTRIBUTE5%TYPE,
P_ATTRIBUTE6 IN JTF_RS_GROUPS_VL.ATTRIBUTE6%TYPE,
P_ATTRIBUTE7 IN JTF_RS_GROUPS_VL.ATTRIBUTE7%TYPE,
P_ATTRIBUTE8 IN JTF_RS_GROUPS_VL.ATTRIBUTE8%TYPE,
P_ATTRIBUTE9 IN JTF_RS_GROUPS_VL.ATTRIBUTE9%TYPE,
P_ATTRIBUTE10 IN JTF_RS_GROUPS_VL.ATTRIBUTE10%TYPE,
P_ATTRIBUTE11 IN JTF_RS_GROUPS_VL.ATTRIBUTE11%TYPE,
P_ATTRIBUTE12 IN JTF_RS_GROUPS_VL.ATTRIBUTE12%TYPE,
P_ATTRIBUTE13 IN JTF_RS_GROUPS_VL.ATTRIBUTE13%TYPE,
P_ATTRIBUTE14 IN JTF_RS_GROUPS_VL.ATTRIBUTE14%TYPE,
P_ATTRIBUTE15 IN JTF_RS_GROUPS_VL.ATTRIBUTE15%TYPE,
P_ATTRIBUTE_CATEGORY IN JTF_RS_GROUPS_VL.ATTRIBUTE_CATEGORY%TYPE,
P_OBJECT_VERSION_NUM IN OUT NOCOPY 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,
P_TIME_ZONE IN JTF_RS_GROUPS_VL.TIME_ZONE%TYPE
) IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE_GROUP';
CURSOR c_group_update(
l_group_id IN NUMBER )
IS
SELECT
group_number,
DECODE(p_group_name, fnd_api.g_miss_char, group_name, p_group_name) group_name,
DECODE(p_group_desc, fnd_api.g_miss_char, group_desc, p_group_desc) group_desc,
DECODE(p_exclusive_flag, fnd_api.g_miss_char, exclusive_flag, NULL, 'N', p_exclusive_flag) exclusive_flag,
DECODE(p_email_address, fnd_api.g_miss_char, email_address, p_email_address) email_address,
DECODE(p_start_date_active, fnd_api.g_miss_date, start_date_active, trunc(p_start_date_active)) start_date_active,
DECODE(p_end_date_active, fnd_api.g_miss_date, end_date_active, trunc(p_end_date_active)) end_date_active,
DECODE(p_accounting_code, fnd_api.g_miss_char, accounting_code, p_accounting_code) accounting_code,
DECODE(p_attribute1, fnd_api.g_miss_char, attribute1, p_attribute1) attribute1,
DECODE(p_attribute2, fnd_api.g_miss_char, attribute2, p_attribute2) attribute2,
DECODE(p_attribute3, fnd_api.g_miss_char, attribute3, p_attribute3) attribute3,
DECODE(p_attribute4, fnd_api.g_miss_char, attribute4, p_attribute4) attribute4,
DECODE(p_attribute5, fnd_api.g_miss_char, attribute5, p_attribute5) attribute5,
DECODE(p_attribute6, fnd_api.g_miss_char, attribute6, p_attribute6) attribute6,
DECODE(p_attribute7, fnd_api.g_miss_char, attribute7, p_attribute7) attribute7,
DECODE(p_attribute8, fnd_api.g_miss_char, attribute8, p_attribute8) attribute8,
DECODE(p_attribute9, fnd_api.g_miss_char, attribute9, p_attribute9) attribute9,
DECODE(p_attribute10, fnd_api.g_miss_char, attribute10, p_attribute10) attribute10,
DECODE(p_attribute11, fnd_api.g_miss_char, attribute11, p_attribute11) attribute11,
DECODE(p_attribute12, fnd_api.g_miss_char, attribute12, p_attribute12) attribute12,
DECODE(p_attribute13, fnd_api.g_miss_char, attribute13, p_attribute13) attribute13,
DECODE(p_attribute14, fnd_api.g_miss_char, attribute14, p_attribute14) attribute14,
DECODE(p_attribute15, fnd_api.g_miss_char, attribute15, p_attribute15) attribute15,
DECODE(p_attribute_category, fnd_api.g_miss_char, attribute_category, p_attribute_category) attribute_category,
DECODE(p_time_zone,fnd_api.g_miss_num,time_zone,p_time_zone) time_zone
FROM jtf_rs_groups_vl
WHERE group_id = l_group_id;
group_rec c_group_update%ROWTYPE;
SELECT min(start_date_active),
max(end_date_active)
FROM jtf_rs_role_relations
WHERE role_resource_type = 'RS_GROUP'
AND role_resource_id = l_group_id
AND nvl(delete_flag, 'N') <> 'Y'
AND end_date_active is not null;
SELECT min(start_date_active)
FROM jtf_rs_role_relations
WHERE role_resource_type = 'RS_GROUP'
AND role_resource_id = l_group_id
AND nvl(delete_flag, 'N') <> 'Y'
AND end_date_active is null;
SELECT min(jrrr.start_date_active),
max(jrrr.end_date_active)
FROM jtf_rs_group_members jrgm,
jtf_rs_role_relations jrrr
WHERE jrgm.group_member_id = jrrr.role_resource_id
AND jrrr.role_resource_type = 'RS_GROUP_MEMBER'
AND nvl(jrrr.delete_flag, 'N') <> 'Y'
AND nvl(jrgm.delete_flag, 'N') <> 'Y'
AND jrgm.group_id = l_group_id
AND jrrr.end_date_active is not null;
SELECT min(jrrr.start_date_active)
FROM jtf_rs_group_members jrgm,
jtf_rs_role_relations jrrr
WHERE jrgm.group_member_id = jrrr.role_resource_id
AND jrrr.role_resource_type = 'RS_GROUP_MEMBER'
AND nvl(jrrr.delete_flag, 'N') <> 'Y'
AND nvl(jrgm.delete_flag, 'N') <> 'Y'
AND jrgm.group_id = l_group_id
AND jrrr.end_date_active is null;
SELECT min(start_date_active),
max(end_date_active)
FROM jtf_rs_grp_relations
WHERE ( group_id = l_group_id
OR related_group_id = l_group_id )
AND nvl(delete_flag, 'N') <> 'Y'
AND end_date_active is not null;
SELECT min(start_date_active)
FROM jtf_rs_grp_relations
WHERE ( group_id = l_group_id
OR related_group_id = l_group_id )
AND nvl(delete_flag, 'N') <> 'Y'
AND end_date_active is null;
SELECT min(jrrr.start_date_active),
max(jrrr.end_date_active)
FROM jtf_rs_team_members jrtm,
jtf_rs_role_relations jrrr
WHERE jrtm.team_member_id = jrrr.role_resource_id
AND jrrr.role_resource_type = 'RS_TEAM_MEMBER'
AND nvl(jrrr.delete_flag, 'N') <> 'Y'
AND nvl(jrtm.delete_flag, 'N') <> 'Y'
AND jrtm.team_resource_id = l_group_id
AND jrtm.resource_type = 'RS_GROUP'
AND jrrr.end_date_active is not null;
SELECT min(jrrr.start_date_active)
FROM jtf_rs_team_members jrtm,
jtf_rs_role_relations jrrr
WHERE jrtm.team_member_id = jrrr.role_resource_id
AND jrrr.role_resource_type = 'RS_TEAM_MEMBER'
AND nvl(jrrr.delete_flag, 'N') <> 'Y'
AND nvl(jrtm.delete_flag, 'N') <> 'Y'
AND jrtm.team_resource_id = l_group_id
AND jrtm.resource_type = 'RS_GROUP'
AND jrrr.end_date_active is null;
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 GU1,
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(RR1.end_date_active,RR2.start_date_active + 1) < RR2.start_date_active OR
RR1.start_date_active > RR2.end_date_active) AND
RR2.end_date_active IS NOT NULL)
OR ( nvl(RR1.end_date_active,RR2.start_date_active + 1) < RR2.start_date_active AND
RR2.end_date_active IS NULL ))
AND G2.exclusive_flag = 'Y'
AND GU1.group_id = G1.group_id
AND GU2.group_id = G2.group_id
AND GU1.usage = GU2.usage
AND G1.group_id <> G2.group_id
AND G1.group_id = l_group_id;
SAVEPOINT update_resource_group_pvt;
'UPDATE_RESOURCE_GROUP',
'B',
'C')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_GROUPS_PVT',
'UPDATE_RESOURCE_GROUP',
'B',
'C')
THEN
jtf_rs_resource_group_cuhk.update_resource_group_pre(
p_group_id => l_group_id,
p_group_name => l_group_name,
p_group_desc => l_group_desc,
p_exclusive_flag => l_exclusive_flag,
p_email_address => l_email_address,
p_start_date_active => l_start_date_active,
p_end_date_active => l_end_date_active,
p_accounting_code => l_accounting_code,
x_return_status => x_return_status);
'UPDATE_RESOURCE_GROUP',
'B',
'V')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_GROUPS_PVT',
'UPDATE_RESOURCE_GROUP',
'B',
'V')
THEN
jtf_rs_resource_group_vuhk.update_resource_group_pre(
p_group_id => l_group_id,
p_group_name => l_group_name,
p_group_desc => l_group_desc,
p_exclusive_flag => l_exclusive_flag,
p_email_address => l_email_address,
p_start_date_active => l_start_date_active,
p_end_date_active => l_end_date_active,
p_accounting_code => l_accounting_code,
x_return_status => x_return_status);
'UPDATE_RESOURCE_GROUP',
'B',
'I')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_GROUPS_PVT',
'UPDATE_RESOURCE_GROUP',
'B',
'I')
THEN
jtf_rs_resource_group_iuhk.update_resource_group_pre(
p_group_id => l_group_id,
p_group_name => l_group_name,
p_group_desc => l_group_desc,
p_exclusive_flag => l_exclusive_flag,
p_email_address => l_email_address,
p_start_date_active => l_start_date_active,
p_end_date_active => l_end_date_active,
p_accounting_code => l_accounting_code,
x_return_status => x_return_status);
OPEN c_group_update(l_group_id);
FETCH c_group_update INTO group_rec;
IF c_group_update%NOTFOUND THEN
IF c_group_update%ISOPEN THEN
CLOSE c_group_update;
jtf_rs_groups_aud_pvt.update_group
(P_API_VERSION => 1,
P_INIT_MSG_LIST => fnd_api.g_false,
P_COMMIT => fnd_api.g_false,
P_GROUP_ID => l_group_id,
P_GROUP_NUMBER => group_rec.group_number,
P_GROUP_NAME => group_rec.group_name,
P_GROUP_DESC => group_rec.group_desc,
P_EXCLUSIVE_FLAG => group_rec.exclusive_flag,
P_EMAIL_ADDRESS => group_rec.email_address,
P_START_DATE_ACTIVE => l_start_date_active,
P_END_DATE_ACTIVE => l_end_date_active,
P_ACCOUNTING_CODE => group_rec.accounting_code,
P_OBJECT_VERSION_NUMBER => p_object_version_num + 1,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data,
P_TIME_ZONE => group_rec.time_zone
);
/* update the wf roles with changes resource group
this should be done before the chnages happens to
the database since we need the old values */
-- Don't care for its success status
BEGIN
jtf_rs_wf_integration_pub.update_resource_group
(P_API_VERSION => 1.0,
P_GROUP_ID => l_group_id,
P_GROUP_NAME => l_group_name,
P_EMAIL_ADDRESS => l_email_address,
P_START_DATE_ACTIVE => l_start_date_active,
P_END_DATE_ACTIVE => l_end_date_active,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
/* Update the row into the table by calling the table handler. */
jtf_rs_groups_pkg.update_row(
x_group_id => l_group_id,
x_group_number => group_rec.group_number,
x_exclusive_flag => group_rec.exclusive_flag,
x_email_address => group_rec.email_address,
x_start_date_active => l_start_date_active,
x_end_date_active => l_end_date_active,
x_group_name => group_rec.group_name,
x_group_desc => group_rec.group_desc,
x_accounting_code => group_rec.accounting_code,
x_object_version_number => l_object_version_num,
x_attribute1 => group_rec.attribute1,
x_attribute2 => group_rec.attribute2,
x_attribute3 => group_rec.attribute3,
x_attribute4 => group_rec.attribute4,
x_attribute5 => group_rec.attribute5,
x_attribute6 => group_rec.attribute6,
x_attribute7 => group_rec.attribute7,
x_attribute8 => group_rec.attribute8,
x_attribute9 => group_rec.attribute9,
x_attribute10 => group_rec.attribute10,
x_attribute11 => group_rec.attribute11,
x_attribute12 => group_rec.attribute12,
x_attribute13 => group_rec.attribute13,
x_attribute14 => group_rec.attribute14,
x_attribute15 => group_rec.attribute15,
x_attribute_category => group_rec.attribute_category,
x_last_update_date => SYSDATE,
x_last_updated_by => jtf_resource_utl.updated_by,
x_last_update_login => jtf_resource_utl.login_id,
x_time_zone => group_rec.time_zone
);
IF c_group_update%ISOPEN THEN
CLOSE c_group_update;
IF c_group_update%ISOPEN THEN
CLOSE c_group_update;
jtf_rs_group_denorm_pvt.update_res_groups
(P_API_VERSION => 1,
P_INIT_MSG_LIST => fnd_api.g_false,
P_COMMIT => fnd_api.g_false,
P_GROUP_ID => l_group_id,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data
);
'UPDATE_RESOURCE_GROUP',
'A',
'C')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_GROUPS_PVT',
'UPDATE_RESOURCE_GROUP',
'A',
'C')
THEN
jtf_rs_resource_group_cuhk.update_resource_group_post(
p_group_id => l_group_id,
p_group_name => l_group_name,
p_group_desc => l_group_desc,
p_exclusive_flag => l_exclusive_flag,
p_email_address => l_email_address,
p_start_date_active => l_start_date_active,
p_end_date_active => l_end_date_active,
p_accounting_code => l_accounting_code,
x_return_status => x_return_status);
'UPDATE_RESOURCE_GROUP',
'A',
'V')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_GROUPS_PVT',
'UPDATE_RESOURCE_GROUP',
'A',
'V')
THEN
jtf_rs_resource_group_vuhk.update_resource_group_post(
p_group_id => l_group_id,
p_group_name => l_group_name,
p_group_desc => l_group_desc,
p_exclusive_flag => l_exclusive_flag,
p_email_address => l_email_address,
p_start_date_active => l_start_date_active,
p_end_date_active => l_end_date_active,
p_accounting_code => l_accounting_code,
x_return_status => x_return_status);
'UPDATE_RESOURCE_GROUP',
'A',
'I')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_GROUPS_PVT',
'UPDATE_RESOURCE_GROUP',
'A',
'I')
THEN
jtf_rs_resource_group_iuhk.update_resource_group_post(
p_group_id => l_group_id,
p_group_name => l_group_name,
p_group_desc => l_group_desc,
p_exclusive_flag => l_exclusive_flag,
p_email_address => l_email_address,
p_start_date_active => l_start_date_active,
p_end_date_active => l_end_date_active,
p_accounting_code => l_accounting_code,
x_return_status => x_return_status);
'UPDATE_RESOURCE_GROUP',
'M',
'M')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_GROUPS_PVT',
'UPDATE_RESOURCE_GROUP',
'M',
'M')
THEN
IF (jtf_rs_resource_group_cuhk.ok_to_generate_msg(
p_group_id => l_group_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 update_resource_group_pvt;
ROLLBACK TO update_resource_group_pvt;
ROLLBACK TO update_resource_group_pvt;
END update_resource_group;