The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_update_project IN VARCHAR2 := 'N',
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
CURSOR c1 IS
SELECT projfunc_currency_code, project_currency_code, org_id, probability_member_id
FROM pa_projects_all -- Bug#3807805 Modified pa_projects to pa_projects_all
WHERE project_id = p_project_id;
IF p_update_project = 'N' AND
((v_c1.probability_member_id IS NULL AND p_expected_approval_date IS NOT NULL) OR
(v_c1.probability_member_id IS NOT NULL AND p_expected_approval_date IS NULL)) AND
is_proj_opp_associated(p_project_id)= 'Y' THEN /* Added for bug 3330438 */
RAISE l_prob_exp_date_valid_error;
PA_PROJ_OPP_ATTRS_PKG.update_row (p_project_id => p_project_id,
p_opportunity_value => NULL,
p_opp_value_currency_code => NULL,
p_projfunc_opp_value => NULL,
p_projfunc_opp_rate_type => NULL,
p_projfunc_opp_rate_date => NULL,
p_project_opp_value => NULL,
p_project_opp_rate_type => NULL,
p_project_opp_rate_date => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
PA_PROJ_OPP_ATTRS_PKG.update_row (p_project_id => p_project_id,
p_opportunity_value => 0,
p_opp_value_currency_code => p_opp_value_currency_code, --changed from NULL to p_opp_value_currency_code for BUg 4129683
p_projfunc_opp_value => 0,
p_projfunc_opp_rate_type => NULL,
p_projfunc_opp_rate_date => NULL,
p_project_opp_value => 0,
p_project_opp_rate_type => NULL,
p_project_opp_rate_date => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
PA_PROJ_OPP_ATTRS_PKG.update_row (p_project_id => p_project_id,
p_opportunity_value => p_opportunity_value,
p_opp_value_currency_code => l_opp_value_currency_code,
p_projfunc_opp_value => p_opportunity_value,
p_projfunc_opp_rate_type => NULL,
p_projfunc_opp_rate_date => NULL,
p_project_opp_value => p_opportunity_value,
p_project_opp_rate_type => NULL,
p_project_opp_rate_date => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
PA_PROJ_OPP_ATTRS_PKG.update_row (p_project_id => p_project_id,
p_opportunity_value => p_opportunity_value,
p_opp_value_currency_code => l_opp_value_currency_code,
p_projfunc_opp_value => l_projfunc_opp_value,
p_projfunc_opp_rate_type => l_default_rate_type,
p_projfunc_opp_rate_date => l_conversion_date,
p_project_opp_value => l_project_opp_value,
p_project_opp_rate_type => l_default_rate_type,
p_project_opp_rate_date => l_conversion_date,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
debug('After update_row');
SELECT opportunity_value, opp_value_currency_code, projfunc_opp_value, projfunc_opp_rate_type, projfunc_opp_rate_date, project_opp_value, project_opp_rate_type, project_opp_rate_date
FROM pa_project_opp_attrs
WHERE project_id = p_source_project_id;
PA_PROJ_OPP_ATTRS_PKG.insert_row ( p_project_id => p_dest_project_id,
p_opportunity_value => v_c1.opportunity_value,
p_opp_value_currency_code => v_c1.opp_value_currency_code,
p_projfunc_opp_value => v_c1.projfunc_opp_value,
p_projfunc_opp_rate_type => v_c1.projfunc_opp_rate_type,
p_projfunc_opp_rate_date => v_c1.projfunc_opp_rate_date,
p_project_opp_value => v_c1.project_opp_value,
p_project_opp_rate_type => v_c1.project_opp_rate_type,
p_project_opp_rate_date => v_c1.project_opp_rate_date,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT project_value, projfunc_currency_code, project_currency_code, expected_approval_date, org_id
FROM pa_projects
WHERE project_id = p_project_id;
PA_PROJ_OPP_ATTRS_PKG.insert_row ( p_project_id => p_project_id,
p_opportunity_value => NULL,
p_opp_value_currency_code => NULL,
p_projfunc_opp_value => NULL,
p_projfunc_opp_rate_type => NULL,
p_projfunc_opp_rate_date => NULL,
p_project_opp_value => NULL,
p_project_opp_rate_type => NULL,
p_project_opp_rate_date => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
PA_PROJ_OPP_ATTRS_PKG.insert_row ( p_project_id => p_project_id,
p_opportunity_value => v_c1.project_value,
p_opp_value_currency_code => v_c1.projfunc_currency_code,
p_projfunc_opp_value => v_c1.project_value,
p_projfunc_opp_rate_type => NULL,
p_projfunc_opp_rate_date => NULL,
p_project_opp_value => v_c1.project_value,
p_project_opp_rate_type => NULL,
p_project_opp_rate_date => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
PA_PROJ_OPP_ATTRS_PKG.insert_row ( p_project_id => p_project_id,
p_opportunity_value => v_c1.project_value,
p_opp_value_currency_code => v_c1.projfunc_currency_code,
p_projfunc_opp_value => v_c1.project_value,
p_projfunc_opp_rate_type => NULL,
p_projfunc_opp_rate_date => NULL,
p_project_opp_value => l_converted_amount,
p_project_opp_rate_type => l_default_rate_type,
p_project_opp_rate_date => l_conversion_date,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
PA_PROJ_OPP_ATTRS_PKG.insert_row ( p_project_id => p_project_id,
p_opportunity_value => v_c1.project_value,
p_opp_value_currency_code => v_c1.projfunc_currency_code,
p_projfunc_opp_value => v_c1.project_value,
p_projfunc_opp_rate_type => NULL,
p_projfunc_opp_rate_date => NULL,
p_project_opp_value => NULL,
p_project_opp_rate_type => NULL,
p_project_opp_rate_date => NULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
PROCEDURE delete_project_attributes
( p_project_id IN NUMBER ,
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
CURSOR c1 IS
SELECT object_id_from1 request_id
FROM pa_object_relationships
WHERE relationship_type = 'A'
AND object_type_from = 'PA_PROJECT_REQUESTS'
AND object_type_to = 'PA_PROJECTS'
AND object_id_to1 = p_project_id;
SELECT object_relationship_id, record_version_number
FROM pa_object_relationships
WHERE ((object_type_from = 'PA_PROJECT_REQUESTS'
AND object_type_to = 'PA_PROJECTS'
AND object_id_from1 = l_request_id
AND object_id_to1 = p_project_id) OR
(object_type_from = 'PA_PROJECTS'
AND object_type_to = 'PA_PROJECT_REQUESTS'
AND object_id_from1 = p_project_id
AND object_id_to1 = l_request_id))
AND relationship_type = 'A'
AND relationship_subtype = 'PROJECT_REQUEST';
PA_PROJ_OPP_ATTRS_PKG.delete_row(p_project_id => p_project_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
PA_PROJECT_REQUEST_PKG.update_row(p_request_id => v_c1.request_id,
p_request_status_code => '121',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
PA_OBJECT_RELATIONSHIPS_PKG.delete_row (
p_object_relationship_id => v_c2.object_relationship_id,
p_object_type_from => NULL,
p_object_id_from1 => NULL,
p_object_id_from2 => NULL,
p_object_id_from3 => NULL,
p_object_id_from4 => NULL,
p_object_id_from5 => NULL,
p_object_type_to => NULL,
p_object_id_to1 => NULL,
p_object_id_to2 => NULL,
p_object_id_to3 => NULL,
p_object_id_to4 => NULL,
p_object_id_to5 => NULL,
p_record_version_number => v_c2.record_version_number,
p_pm_product_code => NULL,
x_return_status => x_return_status);
p_procedure_name => 'delete_project_attributes');
END delete_project_attributes;
SELECT default_rate_type
INTO x_default_rate_type
FROM pa_implementations;
SELECT default_rate_type
INTO x_default_rate_type
FROM pa_implementations_all
WHERE org_id = p_org_id; --MOAC Changes: Bug 4363092: removed nvl usage with org_id
p_procedure_name => 'delete_project_attributes');
SELECT default_rate_type
FROM pa_implementations;
SELECT default_rate_type
FROM pa_implementations_all
WHERE org_id = p_org_id; --MOAC Changes: Bug 4363092: removed nvl usage with org_id
SELECT object_type_from, object_type_to
FROM pa_object_relationships
WHERE relationship_type = 'A'
AND relationship_subtype = 'PROJECT_REQUEST'
START WITH (object_type_from = 'PA_PROJECTS'
AND object_type_to = 'PA_PROJECT_REQUESTS'
AND object_id_from1 = p_project_id)
CONNECT BY (PRIOR object_id_to1 = object_id_from1
AND PRIOR object_id_from1 <> object_id_to1);*/
SELECT object_type_from, object_type_to
FROM pa_object_relationships
START WITH (object_type_from = 'PA_PROJECTS'
AND object_type_to = 'PA_PROJECT_REQUESTS'
AND object_id_from1 = p_project_id)
CONNECT BY (PRIOR object_id_to1 = object_id_from1
AND PRIOR object_id_from1 <> object_id_to1
AND PRIOR object_type_to = object_type_from -- bug 9014325
AND relationship_type = 'A'
AND relationship_subtype = 'PROJECT_REQUEST');