[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Update Resource Team 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_TEAMS_PVT';
SELECT 'Y'
FROM jtf_rs_teams_b
WHERE ROWID = l_rowid;
SELECT jtf_rs_teams_s.nextval
INTO l_team_id
FROM dual;
SELECT jtf_rs_team_number_s.nextval
INTO l_team_number
FROM dual;
/* Insert the row into the table by calling the table handler. */
jtf_rs_teams_pkg.insert_row(
x_rowid => l_rowid,
x_team_id => l_team_id,
x_team_number => l_team_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_team_name => l_team_name,
x_team_desc => l_team_desc,
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 update the resource team based on input values
passed by calling routines. */
PROCEDURE update_resource_team
(P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_TEAM_ID IN JTF_RS_TEAMS_VL.TEAM_ID%TYPE,
P_TEAM_NAME IN JTF_RS_TEAMS_VL.TEAM_NAME%TYPE,
P_TEAM_DESC IN JTF_RS_TEAMS_VL.TEAM_DESC%TYPE,
P_EXCLUSIVE_FLAG IN JTF_RS_TEAMS_VL.EXCLUSIVE_FLAG%TYPE,
P_EMAIL_ADDRESS IN JTF_RS_TEAMS_VL.EMAIL_ADDRESS%TYPE,
P_START_DATE_ACTIVE IN JTF_RS_TEAMS_VL.START_DATE_ACTIVE%TYPE,
P_END_DATE_ACTIVE IN JTF_RS_TEAMS_VL.END_DATE_ACTIVE%TYPE,
P_ATTRIBUTE1 IN JTF_RS_TEAMS_VL.ATTRIBUTE1%TYPE,
P_ATTRIBUTE2 IN JTF_RS_TEAMS_VL.ATTRIBUTE2%TYPE,
P_ATTRIBUTE3 IN JTF_RS_TEAMS_VL.ATTRIBUTE3%TYPE,
P_ATTRIBUTE4 IN JTF_RS_TEAMS_VL.ATTRIBUTE4%TYPE,
P_ATTRIBUTE5 IN JTF_RS_TEAMS_VL.ATTRIBUTE5%TYPE,
P_ATTRIBUTE6 IN JTF_RS_TEAMS_VL.ATTRIBUTE6%TYPE,
P_ATTRIBUTE7 IN JTF_RS_TEAMS_VL.ATTRIBUTE7%TYPE,
P_ATTRIBUTE8 IN JTF_RS_TEAMS_VL.ATTRIBUTE8%TYPE,
P_ATTRIBUTE9 IN JTF_RS_TEAMS_VL.ATTRIBUTE9%TYPE,
P_ATTRIBUTE10 IN JTF_RS_TEAMS_VL.ATTRIBUTE10%TYPE,
P_ATTRIBUTE11 IN JTF_RS_TEAMS_VL.ATTRIBUTE11%TYPE,
P_ATTRIBUTE12 IN JTF_RS_TEAMS_VL.ATTRIBUTE12%TYPE,
P_ATTRIBUTE13 IN JTF_RS_TEAMS_VL.ATTRIBUTE13%TYPE,
P_ATTRIBUTE14 IN JTF_RS_TEAMS_VL.ATTRIBUTE14%TYPE,
P_ATTRIBUTE15 IN JTF_RS_TEAMS_VL.ATTRIBUTE15%TYPE,
P_ATTRIBUTE_CATEGORY IN JTF_RS_TEAMS_VL.ATTRIBUTE_CATEGORY%TYPE,
P_OBJECT_VERSION_NUM IN OUT NOCOPY JTF_RS_TEAMS_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_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_RESOURCE_TEAM';
CURSOR c_team_update(
l_team_id IN NUMBER )
IS
SELECT
team_number,
DECODE(p_team_name, fnd_api.g_miss_char, team_name, p_team_name) team_name,
DECODE(p_team_desc, fnd_api.g_miss_char, team_desc, p_team_desc) team_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_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
FROM jtf_rs_teams_vl
WHERE team_id = l_team_id;
team_rec c_team_update%ROWTYPE;
SELECT min(start_date_active),
max(end_date_active)
FROM jtf_rs_role_relations
WHERE role_resource_type = 'RS_TEAM'
AND role_resource_id = l_team_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_TEAM'
AND role_resource_id = l_team_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 jrgm,
jtf_rs_role_relations jrrr
WHERE jrgm.team_member_id = jrrr.role_resource_id
AND jrrr.role_resource_type = 'RS_TEAM_MEMBER'
AND nvl(jrrr.delete_flag, 'N') <> 'Y'
AND nvl(jrgm.delete_flag, 'N') <> 'Y'
AND jrgm.team_id = l_team_id
AND jrrr.end_date_active is not null;
SELECT min(jrrr.start_date_active)
FROM jtf_rs_team_members jrgm,
jtf_rs_role_relations jrrr
WHERE jrgm.team_member_id = jrrr.role_resource_id
AND jrrr.role_resource_type = 'RS_TEAM_MEMBER'
AND nvl(jrrr.delete_flag, 'N') <> 'Y'
AND nvl(jrgm.delete_flag, 'N') <> 'Y'
AND jrgm.team_id = l_team_id
AND jrrr.end_date_active is null;
SELECT 'Y'
FROM jtf_rs_teams_vl T1,
jtf_rs_teams_vl T2,
jtf_rs_team_members TM1,
jtf_rs_team_members TM2,
jtf_rs_team_usages TU1,
jtf_rs_team_usages TU2,
jtf_rs_role_relations RR1,
jtf_rs_role_relations RR2
WHERE T1.team_id = TM1.team_id
AND T2.team_id = TM2.team_id
AND nvl(TM1.delete_flag, 'N') <> 'Y'
AND nvl(TM2.delete_flag, 'N') <> 'Y'
AND TM1.team_resource_id = TM2.team_resource_id
AND TM1.resource_type = TM2.resource_type
AND TM1.team_member_id = RR1.role_resource_id
AND TM2.team_member_id = RR2.role_resource_id
AND RR1.role_resource_type = 'RS_TEAM_MEMBER'
AND RR2.role_resource_type = 'RS_TEAM_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 T2.exclusive_flag = 'Y'
AND TU1.team_id = T1.team_id
AND TU2.team_id = T2.team_id
AND TU1.usage = TU2.usage
AND T1.team_id <> T2.team_id
AND T1.team_id = l_team_id;
SAVEPOINT update_resource_team_pvt;
'UPDATE_RESOURCE_TEAM',
'B',
'C')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_TEAMS_PVT',
'UPDATE_RESOURCE_TEAM',
'B',
'C')
THEN
jtf_rs_resource_team_cuhk.update_resource_team_pre(
p_team_id => l_team_id,
p_team_name => l_team_name,
p_team_desc => l_team_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,
x_return_status => x_return_status);
'UPDATE_RESOURCE_TEAM',
'B',
'V')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_TEAMS_PVT',
'UPDATE_RESOURCE_TEAM',
'B',
'V')
THEN
jtf_rs_resource_team_vuhk.update_resource_team_pre(
p_team_id => l_team_id,
p_team_name => l_team_name,
p_team_desc => l_team_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,
x_return_status => x_return_status);
'UPDATE_RESOURCE_TEAM',
'B',
'I')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_TEAMS_PVT',
'UPDATE_RESOURCE_TEAM',
'B',
'I')
THEN
jtf_rs_resource_team_iuhk.update_resource_team_pre(
p_team_id => l_team_id,
p_team_name => l_team_name,
p_team_desc => l_team_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,
x_return_status => x_return_status);
OPEN c_team_update(l_team_id);
FETCH c_team_update INTO team_rec;
IF c_team_update%NOTFOUND THEN
IF c_team_update%ISOPEN THEN
CLOSE c_team_update;
/* update the wf roles with changes resource team
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_team
(P_API_VERSION => 1.0,
P_TEAM_ID => l_team_id,
P_TEAM_NAME => l_team_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_teams_pkg.update_row(
x_team_id => l_team_id,
x_team_number => team_rec.team_number,
x_exclusive_flag => team_rec.exclusive_flag,
x_email_address => team_rec.email_address,
x_start_date_active => l_start_date_active,
x_end_date_active => l_end_date_active,
x_team_name => team_rec.team_name,
x_team_desc => team_rec.team_desc,
x_object_version_number => l_object_version_num,
x_attribute1 => team_rec.attribute1,
x_attribute2 => team_rec.attribute2,
x_attribute3 => team_rec.attribute3,
x_attribute4 => team_rec.attribute4,
x_attribute5 => team_rec.attribute5,
x_attribute6 => team_rec.attribute6,
x_attribute7 => team_rec.attribute7,
x_attribute8 => team_rec.attribute8,
x_attribute9 => team_rec.attribute9,
x_attribute10 => team_rec.attribute10,
x_attribute11 => team_rec.attribute11,
x_attribute12 => team_rec.attribute12,
x_attribute13 => team_rec.attribute13,
x_attribute14 => team_rec.attribute14,
x_attribute15 => team_rec.attribute15,
x_attribute_category => team_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
);
IF c_team_update%ISOPEN THEN
CLOSE c_team_update;
IF c_team_update%ISOPEN THEN
CLOSE c_team_update;
'UPDATE_RESOURCE_TEAM',
'A',
'C')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_TEAMS_PVT',
'UPDATE_RESOURCE_TEAM',
'A',
'C')
THEN
jtf_rs_resource_team_cuhk.update_resource_team_post(
p_team_id => l_team_id,
p_team_name => l_team_name,
p_team_desc => l_team_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,
x_return_status => x_return_status);
'UPDATE_RESOURCE_TEAM',
'A',
'V')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_TEAMS_PVT',
'UPDATE_RESOURCE_TEAM',
'A',
'V')
THEN
jtf_rs_resource_team_vuhk.update_resource_team_post(
p_team_id => l_team_id,
p_team_name => l_team_name,
p_team_desc => l_team_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,
x_return_status => x_return_status);
'UPDATE_RESOURCE_TEAM',
'A',
'I')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_TEAMS_PVT',
'UPDATE_RESOURCE_TEAM',
'A',
'I')
THEN
jtf_rs_resource_team_iuhk.update_resource_team_post(
p_team_id => l_team_id,
p_team_name => l_team_name,
p_team_desc => l_team_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,
x_return_status => x_return_status);
'UPDATE_RESOURCE_TEAM',
'M',
'M')
THEN
IF jtf_usr_hks.ok_to_execute(
'JTF_RS_TEAMS_PVT',
'UPDATE_RESOURCE_TEAM',
'M',
'M')
THEN
IF (jtf_rs_resource_team_cuhk.ok_to_generate_msg(
p_team_id => l_team_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_team_pvt;
ROLLBACK TO update_resource_team_pvt;
END update_resource_team;