[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE do_unmark_primary_flag_update (
p_task_phone_id IN NUMBER,
p_phone_id IN NUMBER,
l_contact_id IN NUMBER
);
SELECT '1'
FROM jtf_task_phones
WHERE task_contact_id = b_task_contact_id
AND owner_table_name = b_owner_table_name;
SELECT 1
FROM jtf_task_phones
WHERE ROWID = l_rowid;
SELECT jtf_task_phones_s.nextval
INTO l_task_phone_id
FROM dual;
jtf_task_phones_pkg.insert_row (
x_rowid => l_rowid,
x_task_phone_id => l_task_phone_id,
x_task_contact_id => p_task_contact_id,
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_phone_id => p_phone_id,
x_creation_date => SYSDATE,
x_created_by => jtf_task_utl.created_by,
x_last_update_date => SYSDATE,
x_last_updated_by => jtf_task_utl.updated_by,
x_last_update_login => jtf_task_utl.login_id,
x_owner_table_name => p_owner_table_name,
x_primary_flag => l_primary_flag -- For fixing a bug 2644132
);
PROCEDURE update_task_phones (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_object_version_number IN OUT NOCOPY NUMBER,
p_task_phone_id IN NUMBER,
p_phone_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
p_attribute1 IN VARCHAR2,
p_attribute2 IN VARCHAR2,
p_attribute3 IN VARCHAR2,
p_attribute4 IN VARCHAR2,
p_attribute5 IN VARCHAR2,
p_attribute6 IN VARCHAR2,
p_attribute7 IN VARCHAR2,
p_attribute8 IN VARCHAR2,
p_attribute9 IN VARCHAR2,
p_attribute10 IN VARCHAR2,
p_attribute11 IN VARCHAR2,
p_attribute12 IN VARCHAR2,
p_attribute13 IN VARCHAR2,
p_attribute14 IN VARCHAR2,
p_attribute15 IN VARCHAR2,
p_attribute_category IN VARCHAR2,
p_primary_flag IN VARCHAR2
)
IS
l_api_version CONSTANT NUMBER := 1.0;
:= 'UPDATE_TASK_PHONES';
SELECT 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_primary_flag,
fnd_api.g_miss_char, primary_flag,
p_primary_flag
) primary_flag,
owner_table_name
FROM jtf_task_phones
WHERE task_phone_id = p_task_phone_id;
SAVEPOINT update_task_phones_pub;
SELECT task_contact_id,
NVL (owner_table_name, 'JTF_TASK_CONTACTS')
INTO l_task_contact_id,
l_owner_table_name
FROM jtf_task_phones
WHERE task_phone_id = p_task_phone_id;
SELECT contact_id
INTO l_contact_id
FROM jtf_task_contacts
WHERE task_contact_id = l_task_contact_id;
SELECT customer_id
INTO l_contact_id
FROM jtf_tasks_b
WHERE task_id = l_task_contact_id;
SELECT 1
INTO x
FROM jtf_task_phones
WHERE task_phone_id = p_task_phone_id;
do_unmark_primary_flag_update (
p_task_phone_id,
p_phone_id,
l_contact_id
);
jtf_task_phones_pkg.update_row (
x_task_phone_id => p_task_phone_id,
x_task_contact_id => l_task_contact_id,
x_object_version_number => p_object_version_number + 1,
x_attribute1 => task_phones.attribute1,
x_attribute2 => task_phones.attribute2,
x_attribute3 => task_phones.attribute3,
x_attribute4 => task_phones.attribute4,
x_attribute5 => task_phones.attribute5,
x_attribute6 => task_phones.attribute6,
x_attribute7 => task_phones.attribute7,
x_attribute8 => task_phones.attribute8,
x_attribute9 => task_phones.attribute9,
x_attribute10 => task_phones.attribute10,
x_attribute11 => task_phones.attribute11,
x_attribute12 => task_phones.attribute12,
x_attribute13 => task_phones.attribute13,
x_attribute14 => task_phones.attribute14,
x_attribute15 => task_phones.attribute15,
x_attribute_category => task_phones.attribute_category,
x_phone_id => p_phone_id,
x_last_update_date => SYSDATE,
x_last_updated_by => jtf_task_utl.updated_by,
x_last_update_login => jtf_task_utl.login_id,
x_owner_table_name => task_phones.owner_table_name,
x_primary_flag => task_phones.primary_flag
);
ROLLBACK TO update_task_phones_pub;
ROLLBACK TO update_task_phones_pub;
PROCEDURE delete_task_phones (
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_object_version_number IN NUMBER,
p_task_phone_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_data OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER
)
IS
l_api_version CONSTANT NUMBER := 1.0;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_TASK_PHONES';
SELECT 1
FROM jtf_task_phones
WHERE task_phone_id = p_task_phone_id;
SAVEPOINT delete_task_phones_pub;
jtf_task_phones_pkg.delete_row (x_task_phone_id => p_task_phone_id);
ROLLBACK TO delete_task_phones_pub;
ROLLBACK TO delete_task_phones_pub;
UPDATE jtf_task_phones
SET primary_flag = 'N'
WHERE task_contact_id = p_task_contact_id
AND phone_id IN
( SELECT contact_point_id
FROM hz_contact_points
WHERE contact_point_type =
(SELECT contact_point_type
FROM hz_contact_points
WHERE contact_point_id = p_phone_id
AND owner_table_id =
(SELECT contact_id
FROM jtf_task_contacts
WHERE task_contact_id =
p_task_contact_id))
AND owner_table_id =
(SELECT contact_id
FROM jtf_task_contacts
WHERE task_contact_id = p_task_contact_id))
AND primary_flag = 'Y';
UPDATE jtf_task_phones
SET primary_flag = 'N'
WHERE task_contact_id = p_task_contact_id
AND phone_id IN
( SELECT contact_point_id
FROM hz_contact_points
WHERE contact_point_type =
(SELECT contact_point_type
FROM hz_contact_points
WHERE contact_point_id =
p_phone_id
AND owner_table_id =
(SELECT customer_id
FROM jtf_tasks_b
WHERE task_id =
p_task_contact_id))
AND owner_table_id =
(SELECT customer_id
FROM jtf_tasks_b
WHERE task_id =
p_task_contact_id))
AND primary_flag = 'Y';
PROCEDURE do_unmark_primary_flag_update (
p_task_phone_id IN NUMBER,
--p_task_contact_id IN NUMBER,
p_phone_id IN NUMBER,
--p_owner_table_name IN VARCHAR2,
l_contact_id IN NUMBER
)
IS
BEGIN
UPDATE jtf_task_phones
SET primary_flag = 'N'
WHERE task_contact_id = (SELECT task_contact_id
FROM jtf_task_phones
WHERE task_phone_id = p_task_phone_id)
AND phone_id IN
( SELECT contact_point_id
FROM hz_contact_points
WHERE contact_point_type =
(SELECT contact_point_type
FROM hz_contact_points
WHERE contact_point_id = p_phone_id
AND owner_table_id = l_contact_id
)
AND owner_table_id = l_contact_id
)
AND primary_flag = 'Y';