[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
, X_insert_update_mode IN VARCHAR2
, X_calling_module IN VARCHAR2
, X_project_id IN NUMBER
, X_task_id IN NUMBER
, X_old_value IN VARCHAR2
, X_new_value IN VARCHAR2
, X_project_type IN VARCHAR2
, X_project_start_date IN DATE
, X_project_end_date IN DATE
, X_public_sector_flag IN VARCHAR2
, X_task_manager_person_id IN NUMBER
, X_Service_type IN VARCHAR2
, X_task_start_date IN DATE
, X_task_end_date IN DATE
, X_entered_by_user_id IN NUMBER
, X_attribute_category IN VARCHAR2
, X_attribute1 IN VARCHAR2
, X_attribute2 IN VARCHAR2
, X_attribute3 IN VARCHAR2
, X_attribute4 IN VARCHAR2
, X_attribute5 IN VARCHAR2
, X_attribute6 IN VARCHAR2
, X_attribute7 IN VARCHAR2
, X_attribute8 IN VARCHAR2
, X_attribute9 IN VARCHAR2
, X_attribute10 IN VARCHAR2
, X_pm_product_code IN VARCHAR2
, X_pm_project_reference IN VARCHAR2
, X_pm_task_reference IN VARCHAR2
, X_functional_security_flag IN VARCHAR2
, x_warnings_only_flag OUT NOCOPY varchar2 --bug3134205 --File.Sql.39 bug 4440895
, X_err_code IN OUT NOCOPY number --File.Sql.39 bug 4440895
, X_err_stage IN OUT NOCOPY varchar2 --File.Sql.39 bug 4440895
, X_err_stack IN OUT NOCOPY varchar2) --File.Sql.39 bug 4440895
IS
x_yes_no varchar2(1);
select nvl(decode(pt.project_type_class_code,'INDIRECT',org_information1
, 'CAPITAL',org_information12
, 'CONTRACT',org_information13),'Y')
,meaning
from hr_organization_information org
, pa_project_types_all pt -- Bug#3807805 : Modified pa_project_types to pa_project_types_all
, pa_lookups lps
where org.organization_id(+) = nvl(x_new_value,x_old_value)
and upper(org.org_information_context(+)) = upper('Project Type Class Information')
and pt.project_type = x_project_type
and lps.lookup_type(+) = 'PROJECT TYPE CLASS'
and lps.lookup_code(+) = pt.project_type_class_code
and pt.org_id = PA_PROJECT_REQUEST_PVT.G_ORG_ID; -- Added the and condition for Bug#3807805
select org_id into PA_PROJECT_REQUEST_PVT.G_ORG_ID from pa_project_types where project_type = X_project_type;
X_insert_update_mode =>X_insert_update_mode
, X_calling_module =>X_calling_module
, X_project_id =>X_project_id
, X_task_id =>X_task_id
, X_old_organization_id =>to_number(X_old_value)
, X_new_organization_id =>to_number(x_new_value)
, X_project_type =>X_project_type
, X_project_start_date =>X_project_start_date
, X_project_end_date =>X_project_end_date
, X_public_sector_flag =>X_public_sector_flag
, X_task_manager_person_id =>X_task_manager_person_id
, X_Service_type =>X_Service_type
, X_task_start_date =>X_task_start_date
, X_task_end_date =>X_task_end_date
, X_entered_by_user_id =>X_entered_by_user_id
, X_attribute_category =>X_attribute_category
, X_attribute1 =>X_attribute1
, X_attribute2 =>X_attribute2
, X_attribute3 =>X_attribute3
, X_attribute4 =>X_attribute4
, X_attribute5 =>X_attribute5
, X_attribute6 =>X_attribute6
, X_attribute7 =>X_attribute7
, X_attribute8 =>X_attribute8
, X_attribute9 =>X_attribute9
, X_attribute10 =>X_attribute10
, X_pm_product_code =>X_pm_product_code
, X_pm_project_reference =>X_pm_project_reference
, X_pm_task_reference =>X_pm_task_reference
, X_functional_security_flag => X_functional_security_flag
, X_outcome =>X_outcome );
select project_system_status_code
into l_system_status_code
from pa_project_statuses
where project_status_code = X_new_value;
select pt.project_type_class_code
into l_proj_type_class_code
from pa_projects_all p, -- Bug#3807805 : Modified pa_projects to pa_projects_all
pa_project_types_all pt -- Bug#3807805 : Modified pa_project_types to pa_project_types_all
where p.project_id = X_Project_ID
and p.project_type = pt.project_type
and p.org_id = pt.org_id; -- Added the and condition for Bug#3807805
update pa_projects_all
set project_status_code = 'APPROVED'
where project_id = x_project_id;
SELECT impl.business_group_id
INTO l_bg_id
FROM pa_implementations_all impl,
pa_projects_all pap
WHERE pap.project_id = p_project_id
AND pap.org_id = impl.org_id; --MOAC Changes: Bug 4363092: removed nvl usage with org_id
SELECT project_id
FROM pa_projects_all
WHERE segment1 = p_project_number;
SELECT project_id
INTO x_project_id
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT project_id
INTO x_project_id
FROM pa_projects_all
WHERE segment1 = p_project_number;
SELECT ps.workflow_item_type
FROM pa_project_statuses ps,
pa_projects_all ppa
WHERE ppa.project_id = p_project_id
AND ppa.project_status_code = ps.project_status_code
AND ps.enable_wf_flag = 'Y'
AND ps.wf_success_status_code is NOT NULL
AND ps.wf_failure_status_code is NOT NULL;
SELECT MAX(item_key)
FROM pa_wf_processes
WHERE item_type = p_wf_item_type
AND entity_key1 = p_project_id
AND wf_type_code = 'PROJECT';
SELECT a.old_project_status_code, a.new_project_status_code
FROM ( SELECT obj_status_change_id,
old_project_status_code,
new_project_status_code
FROM pa_obj_status_changes
WHERE object_type = 'PA_PROJECTS'
AND object_id = p_project_id
ORDER BY obj_status_change_id DESC ) a
WHERE ROWNUM = 1;