[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select 'X' from pa_distribution_lists
where list_id = p_list_id;
Select list_id
from pa_distribution_lists
where name = p_list_name
and (p_list_id is null
OR p_list_id <> list_id) ;
Select list_item_id
from pa_dist_list_items
where list_id = p_list_id
and recipient_type = p_recipient_type
and recipient_id = p_recipient_id;
Select list_id
from pa_distribution_lists
where name = p_list_name;
Select 'X'
from pa_lookups
where lookup_type = 'PA_RECIPIENT_TYPES'
and lookup_code = p_recipient_type;
Select 'X' from fnd_menus
where menu_id = p_menu_id;
SELECT list_id
FROM pa_object_dist_lists
WHERE object_type = p_object_type
AND object_id = p_object_id;
/* Bug 2717635 in the following query, selected from table pa_project_parties
in place of view pa_project_parties_v to improve the performance
Also passed another parameter to this cursor c_access_level ie
cp_resource_id which passes the resource_id for a corresponding user_id
for join with table pa_project_parties
Also there is a cartesian joint with fnd_user which is not required, hence removing it*/
CURSOR c_access_level(cp_list_id NUMBER) IS -- Bug 2717635
SELECT MAX(access_level) access_level FROM (
SELECT access_level
FROM pa_dist_list_items i,
pa_project_parties p -- Bug 2717635
WHERE i.list_id = cp_list_id
AND i.recipient_type = 'PROJECT_PARTY'
AND p.project_party_id = i.recipient_id
AND p.resource_id = l_resource_id -- Bug 2717635
AND p.object_type = l_object_type
AND p.object_id = l_object_id
UNION ALL
SELECT access_level
FROM pa_dist_list_items i,
pa_project_parties p -- Bug 2717635
/* fnd_user u */ -- Bug 2717635
WHERE i.list_id = cp_list_id
AND i.recipient_type = 'PROJECT_ROLE'
AND p.project_role_id = i.recipient_id
AND p.resource_id = l_resource_id -- Bug 2717635
AND p.object_type = l_object_type
AND p.object_id = l_object_id
UNION ALL
SELECT access_level
FROM pa_dist_list_items
WHERE list_id = cp_list_id
AND recipient_type = 'ALL_PROJECT_PARTIES'
AND EXISTS (SELECT 'Y' FROM pa_project_parties -- Bug 2717635
WHERE resource_id = l_resource_id -- Bug 2717635
AND object_type = l_object_type
AND object_id = l_object_id)
UNION ALL
SELECT access_level
FROM pa_dist_list_items
WHERE list_id = cp_list_id
AND recipient_type = 'HZ_PARTY'
AND recipient_id = l_party_id);
SELECT object_type, object_id
INTO l_object_type, l_object_id
FROM pa_object_page_layouts
WHERE object_page_layout_id = p_object_id;
SELECT list_id
FROM pa_object_dist_lists
WHERE object_type = p_object_type
AND object_id = p_object_id;
SELECT DISTINCT user_name, full_name, email_address FROM (
SELECT p.user_name user_name,
p.resource_source_name full_name,
p.email_address email_address
FROM pa_dist_list_items i,
pa_project_parties_v p,
fnd_user u
WHERE i.list_id = cp_list_id
AND i.access_level >= p_access_level
AND i.recipient_type = 'PROJECT_PARTY'
AND p.project_party_id = i.recipient_id
AND p.object_type = l_object_type
AND p.object_id = l_object_id
AND u.user_name=p.user_name
AND (TRUNC(SYSDATE) BETWEEN TRUNC(u.start_date) AND NVL(TRUNC(u.end_date),SYSDATE))
AND trunc(sysdate) between p.start_date_active and nvl(p.end_date_active,sysdate + 1)
UNION ALL
SELECT p.user_name user_name,
p.resource_source_name full_name,
p.email_address email_address
FROM pa_dist_list_items i,
pa_project_parties_v p,
fnd_user u
WHERE i.list_id = cp_list_id
AND i.access_level >= p_access_level
AND i.recipient_type = 'PROJECT_ROLE'
AND p.project_role_id = i.recipient_id
AND p.object_type = l_object_type
AND p.object_id = l_object_id
AND u.user_name=p.user_name
AND (TRUNC(SYSDATE) BETWEEN TRUNC(u.start_date) AND NVL(TRUNC(u.end_date),SYSDATE))
AND trunc(sysdate) between p.start_date_active and nvl(p.end_date_active,sysdate + 1)
UNION ALL
SELECT p.user_name user_name,
p.resource_source_name full_name,
p.email_address email_address
FROM pa_project_parties_v p,
fnd_user u
WHERE EXISTS (SELECT 1 FROM pa_dist_list_items i
WHERE i.list_id = cp_list_id
AND i.access_level >= p_access_level
AND i.recipient_type = 'ALL_PROJECT_PARTIES')
AND p.object_type = l_object_type
AND p.object_id = l_object_id
AND u.user_name=p.user_name
AND (TRUNC(SYSDATE) BETWEEN TRUNC(u.start_date) AND NVL(TRUNC(u.end_date),SYSDATE))
AND trunc(sysdate) between p.start_date_active and nvl(p.end_date_active,sysdate + 1)
UNION ALL
SELECT u.user_name user_name,
hzp.party_name full_name,
hzp.email_address email_address
FROM pa_dist_list_items i,
hz_parties hzp,
fnd_user u
WHERE i.list_id = cp_list_id
AND i.access_level >= p_access_level
AND i.recipient_type = 'HZ_PARTY'
AND hzp.party_id = i.recipient_id
AND SUBSTR(hzp.orig_system_reference, 1, 3) <> 'PER'
AND u.person_party_id (+) = hzp.party_id
-- Bug 4527617. Replaced customer_id with person_party_id.
AND (TRUNC(SYSDATE) BETWEEN TRUNC(u.start_date) AND NVL(TRUNC(u.end_date),SYSDATE))
UNION ALL
SELECT u.user_name user_name,
per.full_name full_name,
per.email_address email_address
FROM pa_dist_list_items i,
per_all_people_f per,
fnd_user u
WHERE i.list_id = cp_list_id
AND i.access_level >= p_access_level
AND i.recipient_type = 'HZ_PARTY'
AND per.party_id = i.recipient_id
--Bug 2722021 added filter on eff dates
AND (TRUNC(SYSDATE) BETWEEN TRUNC(per.effective_start_date)
AND TRUNC(per.effective_end_date))
AND u.employee_id (+) = per.person_id
AND (TRUNC(SYSDATE) BETWEEN TRUNC(u.start_date) AND NVL(TRUNC(u.end_date),SYSDATE))
UNION ALL
SELECT NULL user_name,
NULL full_name,
recipient_id email_address
FROM pa_dist_list_items i
WHERE i.list_id = cp_list_id
AND i.access_level >= p_access_level
AND i.recipient_type = 'EMAIL_ADDRESS');
SELECT object_type, object_id
INTO l_object_type, l_object_id
FROM pa_object_page_layouts
WHERE object_page_layout_id = p_object_id;
SELECT list_id
FROM pa_object_dist_lists
WHERE object_type = p_object_type
AND object_id = p_object_id;
* is selected.
* 3. Removed the condition SELECT for ALL_PROJECT_PARTIES as this has nothing to do with
* email notifications.
* 4. Null handled the SELECTs for customers and non team members.
*/
CURSOR c_dist_list(cp_list_id NUMBER) IS
SELECT DISTINCT user_name, full_name, email_address FROM (
SELECT p.user_name user_name,
p.resource_source_name full_name,
p.email_address email_address
FROM pa_dist_list_items i,
pa_project_parties_v p,
fnd_user u
WHERE i.list_id = cp_list_id
AND i.access_level >= p_access_level
AND i.recipient_type = 'PROJECT_PARTY'
AND p.project_party_id = i.recipient_id
AND p.object_type = l_object_type
AND p.object_id = l_object_id
AND u.user_name=p.user_name
AND (TRUNC(SYSDATE) BETWEEN TRUNC(u.start_date) AND NVL(TRUNC(u.end_date),SYSDATE))
AND trunc(sysdate) between p.start_date_active and nvl(p.end_date_active,sysdate + 1)
AND NVL(i.email, 'Y') <> 'N'
UNION ALL
SELECT p.user_name user_name,
p.resource_source_name full_name,
p.email_address email_address
FROM pa_dist_list_items i,
pa_project_parties_v p,
fnd_user u
WHERE i.list_id = cp_list_id
AND i.access_level >= p_access_level
AND i.recipient_type = 'PROJECT_ROLE'
AND p.project_role_id = i.recipient_id
AND p.object_type = l_object_type
AND p.object_id = l_object_id
AND u.user_name=p.user_name
AND (TRUNC(SYSDATE) BETWEEN TRUNC(u.start_date) AND NVL(TRUNC(u.end_date),SYSDATE))
AND trunc(sysdate) between p.start_date_active and nvl(p.end_date_active,sysdate + 1)
AND NVL(i.email, 'Y') <> 'N'
UNION ALL
SELECT u.user_name user_name,
hzp.party_name full_name,
hzp.email_address email_address
FROM pa_dist_list_items i,
hz_parties hzp,
fnd_user u
WHERE i.list_id = cp_list_id
AND i.access_level >= p_access_level
AND i.recipient_type = 'HZ_PARTY'
AND hzp.party_id = i.recipient_id
AND SUBSTR(hzp.orig_system_reference, 1, 3) <> 'PER'
AND u.customer_id (+) = hzp.party_id
AND ((u.customer_id IS NULL) OR
(TRUNC(SYSDATE) BETWEEN TRUNC(u.start_date) AND NVL(TRUNC(u.end_date),SYSDATE)))
AND nvl(i.email, 'Y') <> 'N'
UNION ALL
SELECT u.user_name user_name,
per.full_name full_name,
per.email_address email_address
FROM pa_dist_list_items i,
per_all_people_f per,
fnd_user u
WHERE i.list_id = cp_list_id
AND i.access_level >= p_access_level
AND i.recipient_type = 'HZ_PARTY'
AND per.party_id = i.recipient_id
AND (TRUNC(SYSDATE) BETWEEN TRUNC(per.effective_start_date)
AND TRUNC(per.effective_end_date))
AND u.employee_id (+) = per.person_id
AND ((u.employee_id IS NULL) OR
(TRUNC(SYSDATE) BETWEEN TRUNC(u.start_date) AND NVL(TRUNC(u.end_date),SYSDATE)))
AND nvl(i.email, 'Y') <> 'N'
UNION ALL
SELECT NULL user_name,
NULL full_name,
recipient_id email_address
FROM pa_dist_list_items i
WHERE i.list_id = cp_list_id
AND i.access_level >= p_access_level
AND i.recipient_type = 'EMAIL_ADDRESS'
AND nvl(i.email, 'Y') <> 'N'
-- Send Status Report by email to all project team members
UNION ALL
SELECT DISTINCT p.user_name user_name,
p.resource_source_name full_name,
p.email_address email_address
FROM pa_project_parties_v p,
fnd_user u
WHERE EXISTS ( SELECT 1
FROM pa_dist_list_items i
WHERE i.list_id = cp_list_id
AND i.access_level >= p_access_level
AND i.recipient_type = 'EMAIL_ALL'
)
AND p.object_type = l_object_type
AND p.object_id = l_object_id
AND u.user_name=p.user_name
AND (TRUNC(SYSDATE) BETWEEN TRUNC(u.start_date) AND NVL(TRUNC(u.end_date),SYSDATE))
AND trunc(sysdate) between p.start_date_active and nvl(p.end_date_active,sysdate + 1)
AND p.party_type IN ('EMPLOYEE', 'PERSON')
);
SELECT object_type, object_id
INTO l_object_type, l_object_id
FROM pa_object_page_layouts
WHERE object_page_layout_id = p_object_id;
P_LAST_UPDATED_BY in NUMBER default fnd_global.user_id,
P_LAST_UPDATE_DATE in DATE default sysdate,
P_LAST_UPDATE_LOGIN in NUMBER default fnd_global.user_id,
x_return_status OUT NOCOPY VARCHAR2, --File.Sql.39 bug 4440895
x_msg_count OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
)
IS
-- Enter the procedure variables here. As shown below
l_object_type VARCHAR2(30);
CURSOR get_object IS SELECT object_id, object_type
FROM pa_object_page_layouts
WHERE page_type_code = 'PPR'
AND object_page_layout_id = p_object_id_to;
CURSOR get_list_id_from IS SELECT list_id
FROM pa_object_dist_lists
WHERE object_type = p_object_type_from
AND object_id = p_object_id_from;
CURSOR get_list_id_to IS SELECT list_id
FROM pa_object_dist_lists
WHERE object_type = p_object_type_to
AND object_id = p_object_id_to;
CURSOR get_project_party_to IS SELECT b.project_party_id
FROM pa_project_parties a, pa_project_parties b
WHERE a.project_party_id = l_recipient_id
AND b.object_type = a.object_type
AND b.object_id = l_object_id
AND b.resource_type_id = a.resource_type_id
AND b.resource_source_id = a.resource_source_id
AND b.project_role_id = a.project_role_id;
CURSOR get_list_items IS SELECT recipient_type, recipient_id, access_level, email, menu_id
FROM pa_dist_list_items
WHERE list_id = l_list_id_from;
select pa_distribution_lists_s.nextVal into l_list_id_to from dual;
PA_DISTRIBUTION_LISTS_PVT.UPDATE_DIST_LIST_ITEM(P_VALIDATE_ONLY => 'F',
P_LIST_ITEM_ID => l_list_item_id,
P_LIST_ID => l_list_id_to,
P_RECIPIENT_TYPE => l_recipient_type,
P_RECIPIENT_ID => l_recipient_id,
P_ACCESS_LEVEL => l_access_level,
P_EMAIL => l_email,
P_MENU_ID => l_menu_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
select pa_dist_list_items_s.nextVal into l_list_item_id from dual;
Select 'X' from pa_dist_list_items
where list_item_id = p_list_item_id;