The following lines contain the word 'select', 'insert', 'update' or 'delete':
FUNCTION VALIDATE_DELETE_PARTY_OK (p_project_id IN NUMBER,
p_project_party_id IN NUMBER) RETURN VARCHAR2 IS
BEGIN
RETURN 'Y';
END VALIDATE_DELETE_PARTY_OK;
/* Commenting this query and selecting start date from PA_PROJECT_DATES_UTILS.GET_PROJECT_START_DATE */
/* select start_date, completion_date
into x_project_start_date, x_project_end_date
from pa_projects_all
where project_id = p_project_id; */
SELECT PA_PROJECT_DATES_UTILS.GET_PROJECT_START_DATE(p_project_id), completion_date
INTO x_project_start_date, x_project_end_date
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT 'Y'
FROM pa_project_parties ppp,
pa_project_role_types_b r1,
pa_project_role_types_b r2
WHERE r1.project_role_id = p_project_role_id
AND r1.role_party_class = 'CUSTOMER'
AND ppp.object_id = p_object_id
AND ppp.object_type = p_object_type
AND ppp.resource_type_id = 112
AND ppp.resource_source_id = p_resource_source_id
AND r2.project_role_id = ppp.project_role_id
AND r2.role_party_class = 'CUSTOMER'
AND ROWNUM=1;
SELECT 'Y'
INTO l_dummy
FROM pa_project_role_types_b
WHERE project_role_id = p_project_role_id
AND p_start_date_active BETWEEN start_date_active AND NVL(end_date_active,p_start_date_active)
AND (p_end_date_active IS NULL
OR p_end_date_active BETWEEN start_date_active AND NVL(end_date_active,p_end_date_active));
IF (p_action = 'INSERT' OR pa_project_parties_utils.get_scheduled_flag(p_project_party_id, p_record_version_number) <> p_scheduled_flag) THEN
IF p_resource_type_id = 112 THEN
IF (p_debug_mode = 'Y') THEN
pa_debug.debug('Validate_project_party: Looking for customer org duplicate.');
IF p_scheduled_flag = 'N' AND p_action = 'UPDATE' THEN
IF (p_debug_mode = 'Y') THEN
pa_debug.debug('Validate_project_party: Need to delete schedule.');
x_assignment_action := 'DELETE';
IF x_assignment_action = 'CREATE' OR (p_action = 'INSERT' AND p_calling_module <> 'PROJECT_MEMBER') THEN
--dbms_output.put_line('trying to get person id');
IF x_call_overlap = 'Y' OR p_action = 'UPDATE' OR p_calling_module = 'EXCHANGE' THEN
IF p_project_role_id = 1 THEN -- hard coded for Project Manager
IF (p_debug_mode = 'Y') THEN
pa_debug.debug('Validate_project_party: Calling validate_no_overlap_manager.');
SELECT scheduled_flag INTO l_scheduled_flag
FROM pa_project_parties
WHERE project_party_id = NVL(p_project_party_id,-999)
AND record_version_number = NVL(p_record_version_number,record_version_number);
SELECT project_party_id, record_version_number INTO x_project_party_id, x_record_version_number
FROM pa_project_parties
WHERE object_type = p_object_type
AND object_id = p_object_id
AND project_role_id = p_project_role_id
AND resource_type_id = p_resource_type_id
AND resource_source_id = p_resource_source_id
AND start_date_active = TRUNC(p_start_date_active)
AND end_date_active = TRUNC(p_end_date_active);
SELECT 'Y' INTO l_error_occured
FROM pa_project_parties
WHERE object_type = p_object_type
AND object_id = p_object_id
AND project_role_id = p_project_role_id
AND project_party_id <> NVL(p_project_party_id,-999)
AND (p_start_date_active BETWEEN start_date_active AND NVL(end_date_active,start_date_active)
OR NVL(p_end_date_active, p_start_date_active) BETWEEN start_date_active AND NVL(end_date_active,start_date_active)
OR start_date_active BETWEEN p_start_date_active AND NVL(p_end_date_active,start_date_active+1)
OR NVL(end_Date_active,start_date_active) BETWEEN p_start_date_active AND NVL(p_end_date_active,start_date_active+1)
OR (p_start_date_active > start_date_active AND end_date_active IS NULL));
SELECT 'Y' INTO l_error_occured
FROM pa_project_parties
WHERE object_type = p_object_type
AND object_id = p_object_id
AND project_role_id = p_project_role_id
AND resource_type_id = p_resource_type_id
AND resource_source_id = p_resource_source_id
AND project_party_id <> NVL(p_project_party_id,-999)
AND (p_start_date_active BETWEEN start_date_active AND NVL(end_date_active,start_date_active)
OR NVL(p_end_date_active, p_start_date_active) BETWEEN start_date_active AND NVL(end_date_active,start_date_active)
OR start_date_active BETWEEN p_start_date_active AND NVL(p_end_date_active,start_date_active+1)
OR NVL(end_date_active,start_date_active) BETWEEN p_start_date_active AND NVL(p_end_date_active,start_date_active+1)
OR (p_start_date_active > start_date_active AND end_date_active IS NULL));
SELECT project_role_id INTO l_project_role_id
FROM pa_project_role_types_vl
WHERE (meaning = p_project_role_type AND p_calling_module <> 'FORM')
OR (project_role_type = p_project_role_type AND p_calling_module = 'FORM');
SELECT person_id INTO l_resource_id
FROM pa_employees
WHERE full_name = p_resource_name
AND active = '*';
SELECT grant_id INTO l_grant_id
FROM pa_project_parties
WHERE project_party_id = p_project_party_id;
SELECT ppp.resource_source_id,
ppp.project_party_id,
ppp.project_role_id,
pprt.meaning,
pe.full_name
INTO x_manager_person_id,
x_project_party_id,
x_project_role_id,
x_project_role_name,
x_manager_name
FROM pa_project_parties ppp,
pa_project_role_types pprt,
per_all_people_f pe
WHERE ppp.project_id = p_project_id
AND ppp.project_role_id = 1
AND ppp.project_role_id = pprt.project_role_id
AND ppp.resource_type_id = 101
AND ppp.resource_source_id = pe.person_id
AND TRUNC(SYSDATE) BETWEEN pe.effective_start_date
AND pe.effective_end_date
AND ppp.object_type = 'PA_PROJECTS'
AND TRUNC(SYSDATE) BETWEEN ppp.start_date_active AND NVL(ppp.end_date_active,TRUNC(SYSDATE)+1);
SELECT ppp.resource_source_id,
ppp.project_party_id,
ppp.project_role_id,
pprt.meaning,
pe.full_name
INTO x_manager_person_id,
x_project_party_id,
x_project_role_id,
x_project_role_name,
x_manager_name
FROM pa_project_parties ppp,
pa_project_role_types pprt,
per_all_people_f pe
WHERE ppp.project_id = p_project_id
AND ppp.project_role_id = 1
AND ppp.project_role_id = pprt.project_role_id
AND ppp.resource_type_id = 101
AND ppp.resource_source_id = pe.person_id
AND TRUNC(SYSDATE) BETWEEN pe.effective_start_date
AND pe.effective_end_date
AND ppp.object_type = 'PA_PROJECTS'
AND ppp.start_date_active > TRUNC(SYSDATE)
AND ppp.start_date_active = (SELECT MIN(ppp1.start_date_active)
FROM pa_project_parties ppp1
WHERE ppp1.project_id = p_project_id
AND ppp1.project_role_id = 1
AND ppp1.start_date_active > TRUNC(SYSDATE));
SELECT project_party_id
INTO ret
FROM pa_project_parties p,
pa_project_role_types_b r,
pa_customers_v c
WHERE r.role_party_class = 'CUSTOMER'
AND p.project_role_id = r.project_role_id
AND p.project_id = p_project_id
AND p.resource_type_id = 112
AND c.party_id = p.resource_source_id
AND c.customer_id = p_customer_id;
SELECT party_type
INTO l_party_type
FROM hz_parties
WHERE party_id = p_resource_source_id;
SELECT role_party_class
INTO l_role_party_class
FROM pa_project_role_types_b
WHERE project_role_id = p_project_role_id;
Select PPP.RESOURCE_SOURCE_ID
FROM PA_PROJECT_PARTIES PPP ,
--PA_PROJECT_ROLE_TYPES PPRT --bug 4004821
PA_PROJECT_ROLE_TYPES_B PPRT
WHERE
PPP.PROJECT_ID = p_project_id
AND PPP.PROJECT_ROLE_ID = PPRT.PROJECT_ROLE_ID
AND ppp.resource_type_id = 101 --Added this condition to improve performance. Bug:4752054
AND PPRT.PROJECT_ROLE_TYPE ='PROJECT MANAGER'
AND trunc(c_as_of_date) between trunc(PPP.start_date_active)
AND NVL(trunc(PPP.end_date_active),c_as_of_date);
Select full_name
FROM per_all_people_f
WHERE PERSON_ID = c_person_id
AND trunc(SYSDATE) between trunc(effective_start_date) and trunc(effective_end_date); -- Bug 3283351
Select ppf.full_name
FROM PA_PROJECT_PARTIES PPP,
--PA_PROJECT_ROLE_TYPES PPRT, --bug 4004821
PA_PROJECT_ROLE_TYPES_B PPRT,
per_all_people_f PPF
WHERE
PPP.PROJECT_ID = p_project_id
AND PPP.PROJECT_ROLE_ID = PPRT.PROJECT_ROLE_ID
AND ppp.resource_type_id = 101 --Added this condition to improve performance. Bug:4752054
AND PPRT.PROJECT_ROLE_TYPE ='PROJECT MANAGER'
AND trunc(c_as_of_date) between trunc(PPP.start_date_active)
AND NVL(trunc(PPP.end_date_active),c_as_of_date)
AND ppf.person_id = ppp.resource_source_id
AND trunc(c_as_of_date) between trunc(PPF.effective_start_date) AND trunc(PPF.effective_end_date); -- Added for bug 3283351
select ps.project_system_status_code
,type.project_type_class_code
,proj.project_type -- Added for bug#5098966
,proj.start_date
,proj.completion_date
from pa_projects_all proj
,pa_project_statuses ps
,pa_project_types type
where proj.project_status_code = ps.project_status_code
and proj.project_type = type.project_type
and proj.project_id = p_project_id
and ps.STATUS_TYPE = 'PROJECT';
select person_id
,start_date_active
,end_date_active
from pa_project_players
where project_id = p_project_id
and project_role_type='PROJECT MANAGER'
order by start_date_active;
select project_party_id
,start_date_active
,end_date_active
from pa_project_parties
where project_id = p_project_id
and project_role_id = 1
order by start_date_active;
select person_id
,start_date_active
,end_date_active
from pa_project_players
where project_id = p_project_id
and project_role_type='PROJECT MANAGER'
AND NOT (
exists(
SELECT 'y' FROM dual WHERE start_date_active > l_proj_end_date
)
OR
exists(
SELECT 'y' FROM dual WHERE end_date_active < l_proj_start_date
)
)
order by start_date_active;
select project_party_id
,start_date_active
,end_date_active
from pa_project_parties
where project_id = p_project_id
and project_role_id = 1
AND NOT (
exists(
SELECT 'y' FROM dual WHERE start_date_active > l_proj_end_date
)
OR
exists(
SELECT 'y' FROM dual WHERE end_date_active < l_proj_start_date
)
)
order by start_date_active;
select ps.project_system_status_code
,type.project_type_class_code
from pa_projects_all proj
,pa_project_statuses ps
,pa_project_types type
where proj.project_status_code = ps.project_status_code
and proj.project_type=type.project_type
and proj.project_id = p_project_id
and ps.STATUS_TYPE ='PROJECT';
select count(*)
from pa_project_parties
where project_id = p_project_id
and project_role_id = 1;
Select PPP.RESOURCE_SOURCE_ID
FROM PA_PROJECT_PARTIES PPP ,
--PA_PROJECT_ROLE_TYPES PPRT --bug 4004821
PA_PROJECT_ROLE_TYPES_B PPRT
WHERE
PPP.PROJECT_ID = p_project_id
AND PPP.PROJECT_ROLE_ID = PPRT.PROJECT_ROLE_ID
AND ppp.resource_type_id = 101 --Added this condition to improve performance. Bug:4752054
AND PPRT.PROJECT_ROLE_TYPE ='PROJECT MANAGER'
AND trunc(c_as_of_date) between trunc(PPP.start_date_active)
AND NVL(trunc(PPP.end_date_active),c_as_of_date);
Select ppf.full_name
FROM PA_PROJECT_PARTIES PPP,
--PA_PROJECT_ROLE_TYPES PPRT, --bug 4004821
PA_PROJECT_ROLE_TYPES_B PPRT,
per_all_people_f PPF
WHERE
PPP.PROJECT_ID = p_project_id
AND PPP.PROJECT_ROLE_ID = PPRT.PROJECT_ROLE_ID
AND ppp.resource_type_id = 101 --Added this condition to improve performance. Bug:4752054
AND PPRT.PROJECT_ROLE_TYPE ='PROJECT MANAGER'
AND trunc(c_as_of_date) between trunc(PPP.start_date_active)
AND NVL(trunc(PPP.end_date_active),c_as_of_date)
AND ppf.person_id = ppp.resource_source_id
AND trunc(c_as_of_date) between trunc(PPF.effective_start_date) AND trunc(PPF.effective_end_date); -- Added for bug 3283351