[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT ci_id
FROM pa_control_items
WHERE ci_id = c_Ci_Id;
SELECT ci.project_id,
s.project_system_status_code,
cib.ci_type_class_code,
ci.record_version_number
FROM pa_control_items ci,
pa_ci_types_b cib,
(select
project_status_code,
project_system_status_code
from pa_project_statuses
where status_type = 'CONTROL_ITEM') s
WHERE ci.ci_id = c_Ci_Id
AND ci.ci_type_id = cib.ci_type_id
AND ci.status_code = s.project_status_code;
SELECT ci.ci_id
FROM pa_project_statuses pps, pa_control_items ci
WHERE pps.status_type = 'CONTROL_ITEM'
AND pps.project_status_code = ci.status_code
AND pps.enable_wf_flag = 'Y'
AND pps.wf_success_status_code is not null
AND pps.wf_failure_status_code is not null
AND ci.ci_id = c_Ci_Id;
SELECT pcit.ci_type_class_code
FROM pa_control_items pci, pa_ci_types_b pcit
WHERE pci.ci_id = c_Ci_Id
AND pcit.ci_type_id = pci.ci_type_id;
This update_impacts will do all the necessary validations and call
other procedure to insert the details*/
if l_debug_mode = 'Y' then
pa_debug.g_err_stage:= 'Before Calling the Private API PA_CONTROL_API_PVT.update_impacts';
PA_CONTROL_API_PVT.update_impacts(
p_ci_id => p_ci_id,
x_ci_impact_id => x_impact_id,
p_impact_type_code => l_impact_type_code,
p_impact_description => p_impact_description,
p_api_version_number => p_api_version_number ,
p_commit => p_commit,
p_init_msg_list => p_init_msg_list,
p_mode => 'INSERT',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
This update_impacts will do all the necessary validations and call
other procedure to insert the details*/
if l_debug_mode = 'Y' then
pa_debug.g_err_stage:= 'Before Calling the Private API PA_CONTROL_API_PVT.update_impacts';
PA_CONTROL_API_PVT.update_impacts(
p_ci_id => p_ci_id,
x_ci_impact_id => x_impact_id,
p_impact_type_code => l_impact_type_code,
p_impact_description => p_impact_description,
p_api_version_number => p_api_version_number ,
p_commit => p_commit,
p_init_msg_list => p_init_msg_list,
p_mode => 'INSERT',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
This update_impacts will do all the necessary validations and call
other procedure to insert the details*/
if l_debug_mode = 'Y' then
pa_debug.g_err_stage:= 'Before Calling the Private API PA_CONTROL_API_PVT.update_impacts';
PA_CONTROL_API_PVT.update_impacts(
p_ci_id => p_ci_id,
x_ci_impact_id => x_impact_id,
p_impact_type_code => l_impact_type_code,
p_impact_description => p_impact_description,
p_api_version_number => p_api_version_number ,
p_commit => p_commit,
p_init_msg_list => p_init_msg_list,
p_mode => 'INSERT',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
This update_impacts will do all the necessary validations and call
other procedure to insert the details*/
if l_debug_mode = 'Y' then
pa_debug.g_err_stage:= 'Before Calling the Private API PA_CONTROL_API_PVT.update_impacts';
PA_CONTROL_API_PVT.update_impacts(
p_ci_id => p_ci_id,
x_ci_impact_id => x_impact_id,
p_impact_type_code => l_impact_type_code,
p_impact_description => p_impact_description,
p_api_version_number => p_api_version_number ,
p_commit => p_commit,
p_init_msg_list => p_init_msg_list,
p_mode => 'INSERT',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
pa_debug.g_err_stage:= 'Before Calling the Private API PA_CONTROL_API_PVT.update_impacts';
This update_impacts will do all the necessary validations and call
other procedure to insert the details*/
PA_CONTROL_API_PVT.update_impacts(
p_ci_id => p_ci_id,
x_ci_impact_id => x_impact_id,
p_impact_type_code => l_impact_type_code,
p_impact_description => p_impact_description,
p_api_version_number => p_api_version_number ,
p_mode => 'INSERT',
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
If any of the details did not get inserted successfully then roll back
them including the impact.*/
if x_return_status = FND_API.G_RET_STS_SUCCESS and x_impact_id is not null and p_supplier_det_tbl is not null then
PA_CONTROL_API_PVT.Add_supplier_details(
p_ci_id => p_ci_id,
p_ci_impact_id => x_impact_id,
p_supplier_det_tbl => p_supplier_det_tbl,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
/*Procedure to update or implement the Workplan impact*/
Procedure Update_Workplan_Impact (
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_api_version_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ci_id IN NUMBER := G_PA_MISS_NUM,
p_impact_description IN VARCHAR2 := G_PA_MISS_CHAR
)
IS
--Declaring local Variables
l_impact_type_code pa_ci_impacts.impact_type_code%TYPE:='WORKPLAN';
l_module_name VARCHAR2(200):='PA_CONTROL_API_PUB.Update_Workplan_Impact';
PA_DEBUG.set_curr_function(p_function => 'Update_Workplan_Impact', p_debug_mode => l_debug_mode);
savepoint UPDATE_WORKPLAN_IMPACT_SVPT;
pa_debug.write(l_module_name, 'Start of Update_Workplan_Impact', l_debug_level3);
This update_impacts will do all the necessary validations and call
other procedure to insert the details*/
if l_debug_mode = 'Y' then
pa_debug.g_err_stage:= 'Before Calling the Private API PA_CONTROL_API_PVT.update_impacts';
PA_CONTROL_API_PVT.update_impacts(
p_ci_id => p_ci_id ,
p_impact_type_code => l_impact_type_code,
p_impact_description => p_impact_description,
p_api_version_number => p_api_version_number ,
p_mode => 'UPDATE',
x_ci_impact_id => l_ci_impact_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ROLLBACK to UPDATE_WORKPLAN_IMPACT_SVPT;
ROLLBACK TO UPDATE_WORKPLAN_IMPACT_SVPT;
p_procedure_name => 'UPDATE_WORKPLAN_IMPACT',
p_error_text => x_msg_data);
End Update_Workplan_Impact;
/*Procedure to update or implement the Staffing impact*/
Procedure Update_Staffing_Impact(
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_api_version_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ci_id IN NUMBER := G_PA_MISS_NUM,
p_impact_description IN VARCHAR2 := G_PA_MISS_CHAR
)
IS
--Declaring local Variables
l_impact_type_code pa_ci_impacts.impact_type_code%TYPE:='STAFFING';
l_module_name VARCHAR2(200):='PA_CONTROL_API_PUB.Update_Staffing_Impact';
PA_DEBUG.set_curr_function(p_function => 'Update_Staffing_Impact', p_debug_mode => l_debug_mode);
savepoint UPDATE_STAFFING_IMPACT_SVPT;
pa_debug.write(l_module_name, 'Start of Update_Staffing_Impact', l_debug_level3);
This update_impacts will do all the necessary validations and call
other procedure to insert the details*/
if l_debug_mode = 'Y' then
pa_debug.g_err_stage:= 'Before Calling the Private API PA_CONTROL_API_PVT.update_impacts';
PA_CONTROL_API_PVT.update_impacts(
p_ci_id => p_ci_id ,
p_impact_type_code => l_impact_type_code,
p_impact_description => p_impact_description,
p_api_version_number => p_api_version_number ,
p_mode => 'UPDATE',
x_ci_impact_id => l_ci_impact_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ROLLBACK to UPDATE_STAFFING_IMPACT_SVPT;
ROLLBACK TO UPDATE_STAFFING_IMPACT_SVPT;
p_procedure_name => 'UPDATE_STAFFING_IMPACT',
p_error_text => x_msg_data);
End Update_Staffing_Impact;
/*Procedure to update or implement the Contract impact*/
Procedure Update_Contract_Impact(
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_api_version_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ci_id IN NUMBER := G_PA_MISS_NUM,
p_impact_description IN VARCHAR2 := G_PA_MISS_CHAR
)
IS
--Declaring local Variables
l_impact_type_code pa_ci_impacts.impact_type_code%TYPE:='CONTRACT';
l_module_name VARCHAR2(200):='PA_CONTROL_API_PUB.Update_Contract_Impact';
PA_DEBUG.set_curr_function(p_function => 'Update_Contract_Impact', p_debug_mode => l_debug_mode);
savepoint UPDATE_CONTRACT_IMPACT_SVPT;
pa_debug.write(l_module_name, 'Start of Update_Contract_Impact', l_debug_level3);
This update_impacts will do all the necessary validations and call
other procedure to insert the details*/
if l_debug_mode = 'Y' then
pa_debug.g_err_stage:= 'Before Calling the Private API PA_CONTROL_API_PVT.update_impacts';
PA_CONTROL_API_PVT.update_impacts(
p_ci_id => p_ci_id ,
p_impact_type_code => l_impact_type_code,
p_impact_description => p_impact_description,
p_api_version_number => p_api_version_number ,
p_mode => 'UPDATE',
x_ci_impact_id => l_ci_impact_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ROLLBACK to UPDATE_CONTRACT_IMPACT_SVPT;
ROLLBACK TO UPDATE_CONTRACT_IMPACT_SVPT;
p_procedure_name => 'UPDATE_CONTRACT_IMPACT',
p_error_text => x_msg_data);
End Update_Contract_Impact;
/*Procedure to update or implement the Other impact*/
Procedure Update_Other_Impact(
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_api_version_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ci_id IN NUMBER := G_PA_MISS_NUM,
p_impact_description IN VARCHAR2 := G_PA_MISS_CHAR
)
IS
--Declaring local Variables
l_impact_type_code pa_ci_impacts.impact_type_code%TYPE:='OTHER';
l_module_name VARCHAR2(200):='PA_CONTROL_API_PUB.Update_Other_Impact';
PA_DEBUG.set_curr_function(p_function => 'Update_Other_Impact', p_debug_mode => l_debug_mode);
savepoint UPDATE_OTHER_IMPACT_SVPT;
pa_debug.write(l_module_name, 'Start of Update_Other_Impact', l_debug_level3);
This update_impacts will do all the necessary validations and call
other procedure to insert the details*/
if l_debug_mode = 'Y' then
pa_debug.g_err_stage:= 'Before Calling the Private API PA_CONTROL_API_PVT.update_impacts';
PA_CONTROL_API_PVT.update_impacts(
p_ci_id => p_ci_id ,
p_impact_type_code => l_impact_type_code,
p_impact_description => p_impact_description,
p_api_version_number => p_api_version_number ,
p_mode => 'UPDATE',
x_ci_impact_id => l_ci_impact_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ROLLBACK to UPDATE_OTHER_IMPACT_SVPT;
ROLLBACK TO UPDATE_OTHER_IMPACT_SVPT;
p_procedure_name => 'Update_Other_Impact',
p_error_text => x_msg_data);
End Update_Other_Impact;
Procedure Update_Supplier_Impact (
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_api_version_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ci_id IN NUMBER := G_PA_MISS_NUM,
p_impact_description IN VARCHAR2 := G_PA_MISS_CHAR
)
IS
--Declaring local Variables
l_impact_type_code pa_ci_impacts.impact_type_code%TYPE:='SUPPLIER';
l_module_name VARCHAR2(200):='PA_CONTROL_API_PUB.Update_Supplier_Impact';
PA_DEBUG.set_curr_function(p_function => 'Update_Supplier_Impact', p_debug_mode => l_debug_mode);
savepoint UPDATE_SUPPLIER_IMPACT_SVPT;
pa_debug.write(l_module_name, 'Start of Update_Supplier_Impact', l_debug_level3);
This update_impacts will do all the necessary validations and call
other procedure to insert the details*/
if l_debug_mode = 'Y' then
pa_debug.g_err_stage:= 'Before Calling the Private API PA_CONTROL_API_PVT.update_impacts';
PA_CONTROL_API_PVT.update_impacts(
p_ci_id => p_ci_id ,
p_impact_type_code => l_impact_type_code,
p_impact_description => p_impact_description,
p_api_version_number => p_api_version_number ,
p_mode => 'UPDATE',
x_ci_impact_id => l_ci_impact_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
ROLLBACK to UPDATE_SUPPLIER_IMPACT_SVPT;
ROLLBACK TO UPDATE_SUPPLIER_IMPACT_SVPT;
p_procedure_name => 'Update_Supplier_Impact',
p_error_text => x_msg_data);
END Update_Supplier_Impact;
Procedure Delete_Supplier_Impact_Details
(
P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
P_API_VERSION_NUMBER IN NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2,
P_CI_TRANSACTION_ID IN NUMBER)
IS
-- Local Variables.
l_Msg_Count NUMBER := 0;
l_module_name VARCHAR2(200):='PA_CONTROL_API_PUB.Delete_Supplier_Impact_Details';
SELECT ci_transaction_id ,ci_id
FROM pa_ci_supplier_details
WHERE ci_transaction_id = P_CI_TRANSACTION_ID;
SELECT status_code
FROM pa_control_items
WHERE ci_id = c_ci_id;
PA_DEBUG.Init_Err_Stack ('PA_CONTROL_API_PUB.Delete_Supplier_Impact_Details');
savepoint DELETE_SUPP_DETAIL_SVPT;
/*Security check for the CI_ID UpdateAccess*/
if 'T' <> Pa_ci_security_pkg.check_update_access (l_ci_id) then
PA_UTILS.add_Message( p_app_short_name => 'PA'
,p_msg_name => 'PA_CI_NO_UPDATE_ACCESS');
pa_debug.g_err_stage:= 'the CI_ID does not have the update access';
/* Check for the status control: check whether the action CONTROL_ITEM_ALLOW_UPDATE is allowed on the current status of the issue. */
open c_get_status(l_ci_id);
l_chk_status_ctrl := pa_control_items_utils.CheckCIActionAllowed('CONTROL_ITEM', l_status_code, 'CONTROL_ITEM_ALLOW_UPDATE');
PA_UTILS.ADD_MESSAGE('PA', 'PA_CI_NO_ALLOW_UPDATE');
--Calling the api to delete the supplier details
PA_CI_SUPPLIER_PKG.delete_row (p_ci_transaction_id => P_CI_TRANSACTION_ID);
ROLLBACK TO DELETE_SUPP_DETAIL_SVPT;
p_procedure_name => 'Delete_Supplier_Impact_Details',
p_error_text => x_msg_data);
End Delete_Supplier_Impact_Details;
/* Update Progress API to update the progress and resolution details are also included
to add the resolution*/
Procedure Update_Progress(
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_api_version_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ci_id IN NUMBER := G_PA_MISS_NUM,
p_ci_status_code IN VARCHAR2 := G_PA_MISS_CHAR,
p_status_comment IN VARCHAR2 := G_PA_MISS_CHAR,
p_as_of_date IN DATE := G_PA_MISS_DATE,
p_progress_status_code IN VARCHAR2 := G_PA_MISS_CHAR,
p_progress_overview IN VARCHAR2 := G_PA_MISS_CHAR,
p_resolution_code IN VARCHAR2 := G_PA_MISS_CHAR,
p_resolution_comment IN VARCHAR2 := G_PA_MISS_CHAR
)
IS
/* Cursor to get the control item data*/
cursor c_get_control_item_data
is
select *
from pa_control_items
where ci_id = p_ci_id;
Select project_status_code,
project_status_name
From pa_project_statuses
where (trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
and nvl(end_date_active, trunc(sysdate))
and status_type = 'PROGRESS')
and project_status_code = p_progress_status_code; --need to clarify whether this is a name or code
select
cat.class_category class_category,
cat.class_code description,
cat.class_code_id code
from pa_class_codes cat,
pa_ci_types_b typ
where trunc(sysdate) between cat.start_date_active and
nvl(cat.end_date_active,trunc(sysdate))
and typ.ci_type_id=c_ci_type_id
and cat.class_category=typ.resolution_category
and cat.class_code_id = p_resolution_code;
SELECT cit.ci_type_class_code,
cit.approval_required_flag,
s.next_allowable_status_flag
FROM pa_control_items c,
pa_ci_types_b cit,
pa_project_statuses s
WHERE c.ci_id = p_ci_id
AND c.status_code = s.project_status_code
AND c.ci_type_id =cit.ci_type_id
AND s.status_type = 'CONTROL_ITEM';
l_module_name VARCHAR2(200):='PA_CONTROL_API_PUB.Update_Progress';
PA_DEBUG.set_curr_function(p_function => 'UPDATE_PROGRESS', p_debug_mode => l_debug_mode);
savepoint UPDATE_PROGRESS_SVPT;
pa_debug.write(l_module_name, 'Start of Update Progress ', l_debug_level3);
/*Check if the user can update the item. This requires the user to be owner or
to have project authority or to have open actions and status controls are satisfied.
The page will not be updateable when the current status has approval workflow attached*/
/*Security check for the CI_ID UpdateAccess*/
check_s := pa_ci_security_pkg.check_update_access(p_ci_id);
,p_msg_name => 'PA_CI_NO_UPDATE_ACCESS');
pa_debug.g_err_stage:= 'the CI_ID does not have the update access';
check_s := PA_CONTROL_ITEMS_UTILS.CheckCIActionAllowed ('CONTROL_ITEM', l_curr_status_code , 'CONTROL_ITEM_ALLOW_UPDATE');
,p_msg_name => 'PA_CI_NO_ALLOW_UPDATE');
pa_debug.g_err_stage:= 'the CI_ID does not have the update access for the current status';
This is taken from Update_issue procedure. So If any changes happen in update_issue then this code needs to
be looked in to*/
l_curr_system_status := PA_CONTROL_ITEMS_UTILS.getSystemStatus(l_curr_status_code);
l_sel_clause := ' SELECT ps.project_status_code ';
' and ps.project_status_code in (select project_status_code from pa_project_statuses where status_type = '||'''CONTROL_ITEM'''||
' and project_system_status_code in ( select next_allowable_status_code from pa_next_allow_statuses where status_code = '||
''''||l_curr_status_code||''''||')))'||
' OR '||
' ('||''''||l_next_allow_status_flag||''''||' = '||'''U'''||
' and ps.project_status_code in (select next_allowable_status_code from pa_next_allow_statuses where status_code = '||''''||
l_curr_status_code||''''||'))'||
' OR '||
' ('||''''||l_next_allow_status_flag||''''||' = '||'''A'''||
' and ps.project_status_code in (select project_status_code from pa_project_statuses where status_type = '||'''CONTROL_ITEM'''||
' and project_system_status_code in (select next_allowable_status_code from pa_next_allow_statuses where status_code = '||
''''||l_curr_system_status||''''||'))))'||
' AND ps.project_status_code not in (select wf_success_status_code from pa_project_statuses where status_type = '||
'''CONTROL_ITEM'''||' and wf_success_status_code is not null and wf_failure_status_code is not null)'||
' AND ps.project_status_code not in (select wf_failure_status_code from pa_project_statuses where status_type = '||
'''CONTROL_ITEM'''||' and wf_success_status_code is not null and wf_failure_status_code is not null)'||
' AND decode(ps.project_system_status_code, '||'''CI_CANCELED'''||
', nvl(pa_control_items_utils.CheckCIActionAllowed('||'''CONTROL_ITEM'''||', '||''''||l_curr_status_code||''''||', '||
'''CONTROL_ITEM_ALLOW_CANCEL'''||', null),'||'''N'''||' ),'||'''Y'''||' ) = '||'''Y'''||
' AND decode(ps.project_system_status_code,'||'''CI_WORKING'''||
' ,nvl(pa_control_items_utils.CheckCIActionAllowed('||'''CONTROL_ITEM'''||', '||''''||l_curr_status_code||''''||', '||
'''CONTROL_ITEM_ALLOW_REWORK'''||' ,null),'||'''N'''||' ),'||'''Y'''||' ) = '||'''Y'''||
' AND nvl(pa_control_items_utils.CheckCIActionAllowed('||'''CONTROL_ITEM'''||', '||''''||l_curr_status_code||''''||', '||
'''CONTROL_ITEM_ALLOW_UPDST'''||' ,null),'||'''N'''||' ) = '||'''Y'''||
' AND decode(ps.project_system_status_code,'||'''CI_DRAFT'''||
' ,decode('||''''||l_curr_system_status||''''||', '||'''CI_DRAFT'''||', '||
'''Y'''||' ,'||'''N'''||' ),'||'''Y'''||' ) = '||'''Y'''||
' AND ps.project_status_code = '||''''||p_ci_status_code||'''';
l_where1 := ' AND ps.project_status_code not in (select project_status_code from pa_project_statuses where status_type = '||
'''CONTROL_ITEM'''||' and enable_wf_flag = '||'''Y'''||
' and wf_success_status_code is not null and wf_failure_status_code is not null) ';
pa_debug.g_err_stage:= 'Before caliing the PA_CONTROL_ITEMS_PKG.UPDATE_ROW procedure';
PA_CONTROL_ITEMS_PKG.UPDATE_ROW(
p_ci_id => p_ci_id
,p_ci_type_id => l_ci_type_id
,p_summary => l_summary
,p_status_code => l_ci_status_code
,p_owner_id => l_owner_id
,p_highlighted_flag => null
,p_progress_status_code => l_progress_status_code
,p_progress_as_of_date => l_as_of_date
,p_classification_code => l_classification_code_id
,p_reason_code => l_reason_code_id
,p_record_version_number => l_record_version_number
,p_project_id => l_project_id
,p_last_modified_by_id => l_last_modified_by_id
,p_object_type => l_object_type
,p_object_id => l_object_id
,p_ci_number => l_ci_number
,p_date_required => l_date_required
,p_date_closed => l_date_closed
,p_closed_by_id => l_closed_by_id
,p_description => l_description
,p_status_overview => l_progress_overview
,p_resolution => l_resolution_comment
,p_resolution_code => l_resolution_code_id
,p_priority_code => l_priority_code
,p_effort_level_code => l_effort_level_code
,p_open_action_num => null
,p_price => l_price
,p_price_currency_code => l_price_currency_code
,p_source_type_code => l_source_type_code
,p_source_comment => l_source_comment
,p_source_number => l_source_number
,p_source_date_received => l_source_date_received
,p_source_organization => l_source_organization
,p_source_person => l_source_person
,p_attribute_category => l_attribute_category
,p_attribute1 => l_attribute1
,p_attribute2 => l_attribute2
,p_attribute3 => l_attribute3
,p_attribute4 => l_attribute4
,p_attribute5 => l_attribute5
,p_attribute6 => l_attribute6
,p_attribute7 => l_attribute7
,p_attribute8 => l_attribute8
,p_attribute9 => l_attribute9
,p_attribute10 => l_attribute10
,p_attribute11 => l_attribute11
,p_attribute12 => l_attribute12
,p_attribute13 => l_attribute13
,p_attribute14 => l_attribute14
,p_attribute15 => l_attribute15
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
/* call the insert table handlers of pa_obj_status_changes and pa_ci_comments here */
PA_CONTROL_ITEMS_UTILS.ADD_STATUS_CHANGE_COMMENT( p_object_type => 'PA_CI_TYPES'
,p_object_id => p_ci_id
,p_type_code => 'CHANGE_STATUS'
,p_status_type => 'CONTROL_ITEM'
,p_new_project_status => l_ci_status_code
,p_old_project_status => l_curr_status_code
,p_comment => p_status_comment
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
ROLLBACK to UPDATE_PROGRESS_SVPT;
ROLLBACK TO UPDATE_PROGRESS_SVPT;
p_procedure_name => 'Update_Progress',
p_error_text => x_msg_data);
end Update_Progress;
and What ever user gives those will get inserted in the table as it is. */
if l_debug_mode = 'Y' then
pa_debug.g_err_stage:= 'about to call validate_param_and_create';
and What ever user gives those will get inserted in the table as it is. */
if l_debug_mode = 'Y' then
pa_debug.g_err_stage:= 'about to call validate_param_and_create';
and What ever user gives those will get inserted in the table as it is. */
if l_debug_mode = 'Y' then
pa_debug.g_err_stage:= 'about to call validate_param_and_create';
select sign_off_required_flag, record_version_number, date_required, ci_id, status_code
from pa_ci_actions
where ci_action_id = p_action_id;
select wfi.notification_id,
wfi.item_type,
wfi.item_key
from pa_wf_processes pwp,
wf_item_activity_statuses_v wfi
where pwp.entity_key2 = p_action_id
and pwp.item_type ='PAWFCIAC'
and wfi.item_type = pwp.item_type
and wfi.item_key = pwp.item_key
and wfi.activity_type_code ='NOTICE'
and wfi.activity_status_code ='NOTIFIED';
/*Check whether the user has privilige to update this action or not and whether the passed action is valid or not*/
/*for checking the privilege check the party id of the logged in user with party id of person to whom the action is assigned*/
PA_CONTROL_API_PVT.validate_priv_and_action(
p_ci_id => p_ci_id
,p_action_id => p_action_id
,p_action_number => p_action_number
,x_action_id => l_action_id
,x_assignee_id => l_assignee_id
,x_project_id => l_project_id
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
/*user doesnt have privilige to update the action*/
pa_utils.add_message(p_app_short_name => 'PA',
p_msg_name => 'PA_CI_ACTION_NO_ACCESS');
pa_debug.g_err_stage := 'user doesnt have access to update the action';
p_last_updated_by => fnd_global.user_id,
p_last_update_date => sysdate,
p_last_update_login => fnd_global.login_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
p_last_updated_by => fnd_global.user_id,
p_last_update_date => sysdate,
p_last_update_login => fnd_global.login_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
p_last_updated_by => fnd_global.user_id,
p_last_update_date => sysdate,
p_last_update_login => fnd_global.login_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data
);
select ci_id , record_version_number, created_by , status_code
from pa_ci_actions
where ci_action_id = c_action_id;
/*User can only delete the actions, which are created by him*/
close c_get_ci_id;
,p_msg_name => 'PA_CI_NO_UPDATE_ACCESS');
pa_debug.g_err_stage:= 'Does not have the update access';
Procedure Delete_Issue.
Internally calls the procedure PA_CONTROL_API_PVT.Delete_CI
to delete the Issue. The internal procedure is responsible
for all validations.
*/
Procedure Delete_Issue (
p_Commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
, p_Init_Msg_List IN VARCHAR2 DEFAULT FND_API.G_FALSE
, p_Api_Version_Number IN NUMBER
, p_Ci_Id IN NUMBER
, x_Return_Status OUT NOCOPY VARCHAR2
, x_Msg_Count OUT NOCOPY NUMBER
, x_Msg_Data OUT NOCOPY VARCHAR2
)
IS
-- Local Variables.
l_Msg_Count NUMBER := 0;
l_module_name VARCHAR2(200):= 'PA_CONTROL_API_PUB.Delete_Issue';
PA_DEBUG.set_curr_function(p_function => 'Delete_Issue', p_debug_mode => l_debug_mode);
savepoint DELETE_ISSUE_SVPT;
pa_debug.write(l_module_name, 'Start of Delete_Issue', l_debug_level3);
PA_CONTROL_API_PVT.Delete_CI (
p_Commit => p_Commit
, p_Init_Msg_List => 'F'
, p_Api_Version_Number => p_Api_Version_Number
, p_Ci_Id => p_Ci_Id
, x_Return_Status => x_Return_Status
, x_Msg_Count => x_Msg_Count
, x_Msg_Data => x_Msg_Data
);
ROLLBACK TO DELETE_ISSUE_SVPT;
ROLLBACK TO DELETE_ISSUE_SVPT;
p_Procedure_Name => 'Delete_Issue',
p_Error_Text => SUBSTRB (sqlerrm, 1, 240)
);
END Delete_Issue;
Procedure Delete_Change_Request.
Internally calls the procedure PA_CONTROL_API_PVT.Delete_CI
to delete the Issue. The internal procedure is responsible
for all validations.
*/
Procedure Delete_Change_Request (
p_Commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
, p_Init_Msg_List IN VARCHAR2 DEFAULT FND_API.G_FALSE
, p_Api_Version_Number IN NUMBER
, p_Ci_Id IN NUMBER
, x_Return_Status OUT NOCOPY VARCHAR2
, x_Msg_Count OUT NOCOPY NUMBER
, x_Msg_Data OUT NOCOPY VARCHAR2
)
IS
-- Local Variables.
l_Msg_Count NUMBER := 0;
l_module_name VARCHAR2(200):= 'PA_CONTROL_API_PUB.Delete_Change_Request';
PA_DEBUG.set_curr_function(p_function => 'Delete_Change_Request', p_debug_mode => l_debug_mode);
savepoint DELETE_CR_SVPT;
pa_debug.write(l_module_name, 'Start of Delete_Change_Request', l_debug_level3);
PA_CONTROL_API_PVT.Delete_CI (
p_Commit => p_Commit
, p_Init_Msg_List => 'F'
, p_Api_Version_Number => p_Api_Version_Number
, p_Ci_Id => p_Ci_Id
, x_Return_Status => x_Return_Status
, x_Msg_Count => x_Msg_Count
, x_Msg_Data => x_Msg_Data
);
ROLLBACK TO DELETE_CR_SVPT;
ROLLBACK TO DELETE_CR_SVPT;
p_Procedure_Name => 'Delete_Change_Request',
p_Error_Text => SUBSTRB (sqlerrm, 1, 240)
);
END Delete_Change_Request;
Procedure Delete_Change_Order.
Internally calls the procedure PA_CONTROL_API_PVT.Delete_CI
to delete the Issue. The internal procedure is responsible
for all validations.
*/
Procedure Delete_Change_Order (
p_Commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
, p_Init_Msg_List IN VARCHAR2 DEFAULT FND_API.G_FALSE
, p_Api_Version_Number IN NUMBER
, p_Ci_Id IN NUMBER
, x_Return_Status OUT NOCOPY VARCHAR2
, x_Msg_Count OUT NOCOPY NUMBER
, x_Msg_Data OUT NOCOPY VARCHAR2
)
IS
-- Local Variables.
l_Msg_Count NUMBER := 0;
l_module_name VARCHAR2(200):= 'PA_CONTROL_API_PUB.Delete_Change_Order';
PA_DEBUG.set_curr_function(p_function => 'Delete_Change_Order', p_debug_mode => l_debug_mode);
savepoint DELETE_CO_SVPT;
pa_debug.write(l_module_name, 'Start of Delete_Change_Order', l_debug_level3);
PA_CONTROL_API_PVT.Delete_CI (
p_Commit => p_Commit
, p_Init_Msg_List => 'F'
, p_Api_Version_Number => p_Api_Version_Number
, p_Ci_Id => p_Ci_Id
, x_Return_Status => x_Return_Status
, x_Msg_Count => x_Msg_Count
, x_Msg_Data => x_Msg_Data
);
ROLLBACK TO DELETE_CO_SVPT;
ROLLBACK TO DELETE_CO_SVPT;
p_Procedure_Name => 'Delete_Change_Order',
p_Error_Text => SUBSTRB (sqlerrm, 1, 240)
);
END Delete_Change_Order;
, p_Msg_Name => 'PA_CI_NO_ALLOW_UPDATE'
);
2. Check whether the logged in user has update access on the
Control Item and whether the Control Item in question can
be updated or not based on its current status.
*/
Procedure Add_Related_Items (
p_Commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
, p_Init_Msg_List IN VARCHAR2 DEFAULT FND_API.G_FALSE
, p_Api_Version_Number IN NUMBER
, p_Ci_Id IN NUMBER
, p_Related_Items_Tbl IN REL_ITEM_IN_TABLE_TYPE
, x_Return_Status OUT NOCOPY VARCHAR2
, x_Msg_Count OUT NOCOPY NUMBER
, x_Msg_Data OUT NOCOPY VARCHAR2
)
IS
-- Local Variables.
l_CiId NUMBER(15);
l_UpdateAccess VARCHAR2(1);
l_UpdateAllowed VARCHAR2(1);
l_UpdateAccess := PA_CI_SECURITY_PKG.Check_Update_Access (p_Ci_Id);
l_UpdateAllowed := PA_CONTROL_ITEMS_UTILS.CheckCIActionAllowed ('CONTROL_ITEM', l_StatusCode, 'CONTROL_ITEM_ALLOW_UPDATE', p_Ci_Id);
IF (l_UpdateAllowed = 'Y' AND l_UpdateAccess = 'T') THEN
-- For each Related Item in the passed in array, insert it.
FOR i IN 1..p_Related_Items_Tbl.COUNT LOOP
-- If the Related Ci_Id that is passed in does not
-- exist then report Error to rollback changes made
-- till now.
OPEN Check_Valid_CI (p_Related_Items_Tbl(i));
-- Check if Update Access was denied or not.
IF (l_UpdateAccess <> 'T') THEN
-- Add message to the Error Stack that the user does not
-- have the privilege to update this Control Item.
PA_UTILS.Add_Message (
p_App_Short_Name => 'PA'
, p_Msg_Name => 'PA_CI_UPDATE_NOT_ALLOWED'
);
pa_debug.g_err_stage:= 'User does not have the privilege to update this Control Item.';
IF (l_UpdateAllowed <> 'Y') THEN
-- Add message to the Error Stack that this Control Item
-- cannot be updated in its present status.
PA_UTILS.Add_Message (
p_App_Short_Name => 'PA'
, p_Msg_Name => 'PA_CI_NO_ALLOW_UPDATE'
);
pa_debug.g_err_stage:= 'This Control Item cannot be updated in its present status.';
Procedure Delete_Related_Item.
Procedure for deleting Related Items to a particular Control Item.
Validations done before initiating Delete:
1. Check whether the Control Item is valid or not.
2. Check whether the logged in user has update access on the
Control Item and whether the Control Item in question can
be updated or not based on its current status.
*/
Procedure Delete_Related_Item (
p_Commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
, p_Init_Msg_List IN VARCHAR2 DEFAULT FND_API.G_FALSE
, p_Api_Version_Number IN NUMBER
, p_Ci_Id IN NUMBER
, p_To_Ci_Id IN NUMBER
, x_Return_Status OUT NOCOPY VARCHAR2
, x_Msg_Count OUT NOCOPY NUMBER
, x_Msg_Data OUT NOCOPY VARCHAR2
)
IS
-- Local Variables.
l_CiId NUMBER(15);
l_UpdateAccess VARCHAR2(1);
l_UpdateAllowed VARCHAR2(1);
l_module_name VARCHAR2(200):= 'PA_CONTROL_API_PUB.Delete_Related_Item';
PA_DEBUG.set_curr_function(p_function => 'Delete_Related_Item', p_debug_mode => l_debug_mode);
SAVEPOINT DELETE_RELATED_ITEMS_SVPT;
pa_debug.write(l_module_name, 'Start of Delete_Related_Item', l_debug_level3);
l_UpdateAllowed := PA_CONTROL_ITEMS_UTILS.CheckCIActionAllowed ('CONTROL_ITEM', l_StatusCode, 'CONTROL_ITEM_ALLOW_UPDATE', p_Ci_Id);
l_UpdateAccess := PA_CI_SECURITY_PKG.Check_Update_Access (p_Ci_Id);
IF (l_UpdateAllowed = 'Y' AND l_UpdateAccess = 'T') THEN
if l_debug_mode = 'Y' then
pa_debug.g_err_stage:= 'Before Calling PA_CONTROL_ITEMS_PVT.Delete_Related_Item';
PA_CONTROL_ITEMS_PVT.Delete_Related_Item (
p_Api_Version => p_Api_Version_Number
, p_Init_Msg_List => 'F'
, p_Commit => p_Commit
, p_Validate_Only => 'F'
, p_Ci_Id => p_Ci_Id
, p_Related_Ci_Id => p_To_Ci_Id
, x_Return_Status => x_Return_Status
, x_Msg_Count => x_Msg_Count
, x_Msg_Data => x_Msg_Data
);
IF (l_UpdateAccess <> 'T') THEN
-- Add message to the Error Stack that the user does not
-- have the privilege to update this Control Item.
PA_UTILS.Add_Message (
p_App_Short_Name => 'PA'
, p_Msg_Name => 'PA_CI_UPDATE_NOT_ALLOWED'
);
pa_debug.g_err_stage:= 'User does not have the privilege to update this Control Item.';
IF (l_UpdateAllowed <> 'Y') THEN
-- Add message to the Error Stack that this Control Item
-- cannot be updated in its present status.
PA_UTILS.Add_Message (
p_App_Short_Name => 'PA'
, p_Msg_Name => 'PA_CI_NO_ALLOW_UPDATE'
);
pa_debug.g_err_stage:= 'This Control Item cannot be updated in its present status.';
ROLLBACK TO DELETE_RELATED_ITEMS_SVPT;
ROLLBACK TO DELETE_RELATED_ITEMS_SVPT;
p_Procedure_Name => 'Delete_Related_Item',
p_Error_Text => SUBSTRB (sqlerrm, 1, 240)
);
END Delete_Related_Item;
PROCEDURE UPDATE_ISSUE (
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_api_version_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ci_id IN NUMBER,
P_RECORD_VERSION_NUMBER IN NUMBER := G_PA_MISS_NUM,
P_SUMMARY IN VARCHAR2 := G_PA_MISS_CHAR,
P_DESCRIPTION IN VARCHAR2 := G_PA_MISS_CHAR,
P_OWNER_ID IN NUMBER := G_PA_MISS_NUM,
P_OWNER_COMMENT IN VARCHAR2 := G_PA_MISS_CHAR,
P_CLASSIFICATION_CODE IN NUMBER := G_PA_MISS_NUM,
P_REASON_CODE IN NUMBER := G_PA_MISS_NUM,
P_OBJECT_ID IN NUMBER := G_PA_MISS_NUM,
P_OBJECT_TYPE IN VARCHAR2 := G_PA_MISS_CHAR,
P_CI_NUMBER IN VARCHAR2 := G_PA_MISS_CHAR,
P_DATE_REQUIRED IN DATE := G_PA_MISS_DATE,
P_PRIORITY_CODE IN VARCHAR2 := G_PA_MISS_CHAR,
P_EFFORT_LEVEL_CODE IN VARCHAR2 := G_PA_MISS_CHAR,
P_PRICE IN NUMBER := G_PA_MISS_NUM,
P_PRICE_CURRENCY_CODE IN VARCHAR2 := G_PA_MISS_CHAR,
P_SOURCE_TYPE_CODE IN VARCHAR2 := G_PA_MISS_CHAR,
P_SOURCE_NUMBER IN VARCHAR2 := G_PA_MISS_CHAR,
P_SOURCE_COMMENT IN VARCHAR2 := G_PA_MISS_CHAR,
P_SOURCE_DATE_RECEIVED IN DATE := G_PA_MISS_DATE,
P_SOURCE_ORGANIZATION IN VARCHAR2 := G_PA_MISS_CHAR,
P_SOURCE_PERSON IN VARCHAR2 := G_PA_MISS_CHAR,
P_CI_STATUS_CODE IN VARCHAR2 := G_PA_MISS_CHAR,
P_STATUS_COMMENT IN VARCHAR2 := G_PA_MISS_CHAR,
P_PROGRESS_AS_OF_DATE IN DATE := G_PA_MISS_DATE,
P_PROGRESS_STATUS_CODE IN VARCHAR2 := G_PA_MISS_CHAR,
P_PROGRESS_OVERVIEW IN VARCHAR2 := G_PA_MISS_CHAR,
P_RESOLUTION_CODE IN VARCHAR2 := G_PA_MISS_CHAR,
P_RESOLUTION_COMMENT IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE_CATEGORY IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE1 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE2 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE3 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE4 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE5 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE6 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE7 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE8 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE9 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE10 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE11 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE12 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE13 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE14 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE15 IN VARCHAR2 := G_PA_MISS_CHAR
)
IS
l_data VARCHAR2(2000);
l_check_update_access VARCHAR2(1) := 'F';
l_module VARCHAR2(100) := 'PA_CONTROL_API_PUB.UPDATE_ISSUE';
SELECT *
FROM pa_control_items
WHERE ci_id = p_ci_id;
SELECT enable_wf_flag, wf_success_status_code, wf_failure_status_code
FROM pa_project_statuses
WHERE project_status_code = p_curr_status_code;
SELECT lookup_code
FROM pa_lookups
WHERE lookup_type = p_lookup_type
AND trunc(sysdate) BETWEEN start_date_active AND nvl(end_date_active, trunc(sysdate))
AND enabled_flag = 'Y'
AND lookup_code = p_lookup_code;
SELECT project_status_code
FROM pa_project_statuses
WHERE status_type = p_status_type
AND project_status_code = p_project_status_code
AND trunc(sysdate) BETWEEN start_date_active AND nvl(end_date_active, trunc(sysdate));
SELECT cat.class_code_id
FROM pa_class_codes cat,
pa_ci_types_b typ
WHERE trunc(sysdate) between cat.start_date_active and nvl(cat.end_date_active,trunc(sysdate))
AND typ.ci_type_id = p_ci_type_id
AND cat.class_category = typ.classification_category
AND cat.class_code_id = p_class_code_id;
SELECT cat.class_code_id
FROM pa_class_codes cat,
pa_ci_types_b typ
WHERE trunc(sysdate) between cat.start_date_active and nvl(cat.end_date_active,trunc(sysdate))
AND typ.ci_type_id = p_ci_type_id
AND cat.class_category = typ.reason_category
AND cat.class_code_id = p_reason_code_id;
SELECT cat.class_code_id
FROM pa_class_codes cat,
pa_ci_types_b typ
WHERE trunc(sysdate) between cat.start_date_active and nvl(cat.end_date_active,trunc(sysdate))
AND typ.ci_type_id = p_ci_type_id
AND cat.class_category = typ.resolution_category
AND cat.class_code_id = p_resolution_code_id;
SELECT type.auto_number_flag
FROM pa_ci_types_b type,
pa_control_items ci
WHERE ci.ci_id = p_ci_id
AND ci.ci_type_id = type.ci_type_id;
SELECT ROWID
FROM pa_control_items
WHERE project_id = p_project_id
AND ci_number = p_ci_number
AND ci_id <> p_ci_id
AND ci_type_id = p_ci_type_id;
SELECT cit.ci_type_class_code,
cit.approval_required_flag,
s.next_allowable_status_flag
FROM pa_control_items c,
pa_ci_types_b cit,
pa_project_statuses s
WHERE c.ci_id = p_ci_id
AND c.status_code = s.project_status_code
AND c.ci_type_id =cit.ci_type_id
AND s.status_type = 'CONTROL_ITEM';
select distinct resource_source_name party_name
from PA_PROJECT_PARTIES_V
where party_type <> 'ORGANIZATION'
and resource_party_id = c_owner_id
and project_id = c_project_id;
PA_DEBUG.set_curr_function(p_function => 'UPDATE_ISSUE', p_debug_mode => l_debug_mode);
savepoint UPDATE_ISSUE_SVPT;
pa_debug.write(l_module, 'Start of Update Issue', l_debug_level3);
/* Check if the user can update the item. This requires the user to be owner or to have project authority or
to have open UPDATE actions and status controls are satisfied. */
l_check_update_access := pa_ci_security_pkg.check_update_access(p_ci_id);
IF (l_check_update_access = 'F') THEN
PA_UTILS.ADD_MESSAGE('PA', 'PA_CI_NO_UPDATE_ACCESS');
pa_debug.write(l_module, 'After call to pa_ci_security_pkg.check_update_access', l_debug_level3);
/* Check for the status control: check whether the action CONTROL_ITEM_ALLOW_UPDATE is allowed on the current status of the issue. */
l_chk_status_ctrl := pa_control_items_utils.CheckCIActionAllowed('CONTROL_ITEM', l_curr_status_code, 'CONTROL_ITEM_ALLOW_UPDATE');
PA_UTILS.ADD_MESSAGE('PA', 'PA_CI_NO_ALLOW_UPDATE');
/* The control item will not be updateable if the current status has approval workflow attached. */
OPEN c_submit_status(l_curr_status_code);
l_sel_clause := ' SELECT ps.project_status_code ';
' and ps.project_status_code in (select project_status_code from pa_project_statuses where status_type = '||'''CONTROL_ITEM'''||
' and project_system_status_code in ( select next_allowable_status_code from pa_next_allow_statuses where status_code = '||
''''||l_curr_status_code||''''||')))'||
' OR '||
' ('||''''||l_next_allow_status_flag||''''||' = '||'''U'''||
' and ps.project_status_code in (select next_allowable_status_code from pa_next_allow_statuses where status_code = '||''''||
l_curr_status_code||''''||'))'||
' OR '||
' ('||''''||l_next_allow_status_flag||''''||' = '||'''A'''||
' and ps.project_status_code in (select project_status_code from pa_project_statuses where status_type = '||'''CONTROL_ITEM'''||
' and project_system_status_code in (select next_allowable_status_code from pa_next_allow_statuses where status_code = '||
''''||l_curr_system_status||''''||'))))'||
' AND ps.project_status_code not in (select wf_success_status_code from pa_project_statuses where status_type = '||
'''CONTROL_ITEM'''||' and wf_success_status_code is not null and wf_failure_status_code is not null)'||
' AND ps.project_status_code not in (select wf_failure_status_code from pa_project_statuses where status_type = '||
'''CONTROL_ITEM'''||' and wf_success_status_code is not null and wf_failure_status_code is not null)'||
' AND decode(ps.project_system_status_code, '||'''CI_CANCELED'''||
', nvl(pa_control_items_utils.CheckCIActionAllowed('||'''CONTROL_ITEM'''||', '||''''||l_curr_status_code||''''||', '||
'''CONTROL_ITEM_ALLOW_CANCEL'''||', null),'||'''N'''||' ),'||'''Y'''||' ) = '||'''Y'''||
' AND decode(ps.project_system_status_code,'||'''CI_WORKING'''||
' ,nvl(pa_control_items_utils.CheckCIActionAllowed('||'''CONTROL_ITEM'''||', '||''''||l_curr_status_code||''''||', '||
'''CONTROL_ITEM_ALLOW_REWORK'''||' ,null),'||'''N'''||' ),'||'''Y'''||' ) = '||'''Y'''||
' AND nvl(pa_control_items_utils.CheckCIActionAllowed('||'''CONTROL_ITEM'''||', '||''''||l_curr_status_code||''''||', '||
'''CONTROL_ITEM_ALLOW_UPDST'''||' ,null),'||'''N'''||' ) = '||'''Y'''||
' AND decode(ps.project_system_status_code,'||'''CI_DRAFT'''||
' ,decode('||''''||l_curr_system_status||''''||', '||'''CI_DRAFT'''||', '||
'''Y'''||' ,'||'''N'''||' ),'||'''Y'''||' ) = '||'''Y'''||
' AND ps.project_status_code = '||''''||p_ci_status_code||'''';
l_where1 := ' AND ps.project_status_code not in (select project_status_code from pa_project_statuses where status_type = '||
'''CONTROL_ITEM'''||' and enable_wf_flag = '||'''Y'''||
' and wf_success_status_code is not null and wf_failure_status_code is not null) ';
pa_ci_comments_pkg.insert_row(
p_ci_comment_id => l_ci_comment_id,
p_ci_id => p_ci_id,
p_type_code => 'CHANGE_OWNER',
p_comment_text => l_comment_text,
p_last_updated_by => fnd_global.user_id,
p_created_by => fnd_global.user_id,
p_creation_date => sysdate,
p_last_update_date => sysdate,
p_last_update_login => fnd_global.login_id,
p_ci_action_id => null);
SELECT proj_element_id
INTO l_object_id
FROM PA_FIN_LATEST_PUB_TASKS_V
WHERE project_id = l_project_id
AND proj_element_id = p_object_id;
/* Getting validated in pa_control_items_pvt.update_control_item API. */
ELSIF p_price_currency_code IS NULL THEN
l_price_currency_code := p_price_currency_code;
pa_debug.write(l_module, 'before call to PA_CONTROL_ITEMS_PUB.UPDATE_CONTROL_ITEM', l_debug_level3);
PA_CONTROL_ITEMS_PUB.UPDATE_CONTROL_ITEM (
p_api_version => 1.0
,p_init_msg_list => fnd_api.g_false
,p_commit => FND_API.g_false
,p_validate_only => FND_API.g_false
,p_max_msg_count => FND_API.g_miss_num
,p_ci_id => p_ci_id
,p_ci_type_id => l_ci_type_id
,p_summary => l_summary
,p_status_code => l_ci_status_code
,p_owner_id => l_owner_id
,p_owner_name => null
,p_highlighted_flag => null
,p_progress_status_code => l_progress_status_code
,p_progress_as_of_date => l_progress_as_of_date
,p_classification_code => l_classification_code_id
,p_reason_code => l_reason_code_id
,p_record_version_number => l_record_version_number
,p_project_id => l_project_id
,p_object_type => l_object_type
,p_object_id => l_object_id
,p_object_name => null
,p_ci_number => l_ci_number
,p_date_required => l_date_required
,p_date_closed => l_date_closed
,p_closed_by_id => l_closed_by_id
,p_description => l_description
,p_status_overview => l_progress_overview
,p_resolution => l_resolution_comment
,p_resolution_code => l_resolution_code_id
,p_priority_code => l_priority_code
,p_effort_level_code => l_effort_level_code
,p_open_action_num => null
,p_price => l_price
,p_price_currency_code => l_price_currency_code
,p_source_type_code => l_source_type_code
,p_source_comment => l_source_comment
,p_source_number => l_source_number
,p_source_date_received => l_source_date_received
,p_source_organization => l_source_organization
,p_source_person => l_source_person
,p_attribute_category => l_attribute_category
,p_attribute1 => l_attribute1
,p_attribute2 => l_attribute2
,p_attribute3 => l_attribute3
,p_attribute4 => l_attribute4
,p_attribute5 => l_attribute5
,p_attribute6 => l_attribute6
,p_attribute7 => l_attribute7
,p_attribute8 => l_attribute8
,p_attribute9 => l_attribute9
,p_attribute10 => l_attribute10
,p_attribute11 => l_attribute11
,p_attribute12 => l_attribute12
,p_attribute13 => l_attribute13
,p_attribute14 => l_attribute14
,p_attribute15 => l_attribute15
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
pa_debug.write(l_module, 'after call to PA_CONTROL_ITEMS_PUB.UPDATE_CONTROL_ITEM : x_return_status = '||x_return_status, l_debug_level3);
/* call the insert table handlers of pa_obj_status_changes and pa_ci_comments here */
PA_CONTROL_ITEMS_UTILS.ADD_STATUS_CHANGE_COMMENT( p_object_type => 'PA_CI_TYPES'
,p_object_id => p_ci_id
,p_type_code => 'CHANGE_STATUS'
,p_status_type => 'CONTROL_ITEM'
,p_new_project_status => l_ci_status_code
,p_old_project_status => l_curr_status_code
,p_comment => p_status_comment
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data );
ROLLBACK TO UPDATE_ISSUE_SVPT;
ROLLBACK TO UPDATE_ISSUE_SVPT;
, p_procedure_name => 'UPDATE_ISSUE'
, p_error_text => x_msg_data);
END UPDATE_ISSUE;
PROCEDURE UPDATE_CHANGE_REQUEST (
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_api_version_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ci_id IN NUMBER,
P_RECORD_VERSION_NUMBER IN NUMBER := G_PA_MISS_NUM,
P_SUMMARY IN VARCHAR2 := G_PA_MISS_CHAR,
P_DESCRIPTION IN VARCHAR2 := G_PA_MISS_CHAR,
P_OWNER_ID IN NUMBER := G_PA_MISS_NUM,
P_OWNER_COMMENT IN VARCHAR2 := G_PA_MISS_CHAR,
P_CLASSIFICATION_CODE IN NUMBER := G_PA_MISS_NUM,
P_REASON_CODE IN NUMBER := G_PA_MISS_NUM,
P_OBJECT_ID IN NUMBER := G_PA_MISS_NUM,
P_OBJECT_TYPE IN VARCHAR2 := G_PA_MISS_CHAR,
P_CI_NUMBER IN VARCHAR2 := G_PA_MISS_CHAR,
P_DATE_REQUIRED IN DATE := G_PA_MISS_DATE,
P_PRIORITY_CODE IN VARCHAR2 := G_PA_MISS_CHAR,
P_EFFORT_LEVEL_CODE IN VARCHAR2 := G_PA_MISS_CHAR,
P_PRICE IN NUMBER := G_PA_MISS_NUM,
P_PRICE_CURRENCY_CODE IN VARCHAR2 := G_PA_MISS_CHAR,
P_SOURCE_TYPE_CODE IN VARCHAR2 := G_PA_MISS_CHAR,
P_SOURCE_NUMBER IN VARCHAR2 := G_PA_MISS_CHAR,
P_SOURCE_COMMENT IN VARCHAR2 := G_PA_MISS_CHAR,
P_SOURCE_DATE_RECEIVED IN DATE := G_PA_MISS_DATE,
P_SOURCE_ORGANIZATION IN VARCHAR2 := G_PA_MISS_CHAR,
P_SOURCE_PERSON IN VARCHAR2 := G_PA_MISS_CHAR,
P_CI_STATUS_CODE IN VARCHAR2 := G_PA_MISS_CHAR,
P_STATUS_COMMENT IN VARCHAR2 := G_PA_MISS_CHAR,
P_PROGRESS_AS_OF_DATE IN DATE := G_PA_MISS_DATE,
P_PROGRESS_STATUS_CODE IN VARCHAR2 := G_PA_MISS_CHAR,
P_PROGRESS_OVERVIEW IN VARCHAR2 := G_PA_MISS_CHAR,
P_RESOLUTION_CODE IN VARCHAR2 := G_PA_MISS_CHAR,
P_RESOLUTION_COMMENT IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE_CATEGORY IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE1 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE2 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE3 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE4 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE5 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE6 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE7 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE8 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE9 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE10 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE11 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE12 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE13 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE14 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE15 IN VARCHAR2 := G_PA_MISS_CHAR
)
IS
l_data VARCHAR2(2000);
l_check_update_access VARCHAR2(1) := 'F';
l_module VARCHAR2(100) := 'PA_CONTROL_API_PUB.UPDATE_CHANGE_REQUEST';
SELECT *
FROM pa_control_items
WHERE ci_id = p_ci_id;
SELECT enable_wf_flag, wf_success_status_code, wf_failure_status_code
FROM pa_project_statuses
WHERE project_status_code = p_curr_status_code;
SELECT lookup_code
FROM pa_lookups
WHERE lookup_type = p_lookup_type
AND trunc(sysdate) BETWEEN start_date_active AND nvl(end_date_active, trunc(sysdate))
AND enabled_flag = 'Y'
AND lookup_code = p_lookup_code;
SELECT project_status_code
FROM pa_project_statuses
WHERE status_type = p_status_type
AND project_status_code = p_project_status_code
AND trunc(sysdate) BETWEEN start_date_active AND nvl(end_date_active, trunc(sysdate));
SELECT cat.class_code_id
FROM pa_class_codes cat,
pa_ci_types_b typ
WHERE trunc(sysdate) between cat.start_date_active and nvl(cat.end_date_active,trunc(sysdate))
AND typ.ci_type_id = p_ci_type_id
AND cat.class_category = typ.classification_category
AND cat.class_code_id = p_class_code_id;
SELECT cat.class_code_id
FROM pa_class_codes cat,
pa_ci_types_b typ
WHERE trunc(sysdate) between cat.start_date_active and nvl(cat.end_date_active,trunc(sysdate))
AND typ.ci_type_id = p_ci_type_id
AND cat.class_category = typ.reason_category
AND cat.class_code_id = p_reason_code_id;
SELECT cat.class_code_id
FROM pa_class_codes cat,
pa_ci_types_b typ
WHERE trunc(sysdate) between cat.start_date_active and nvl(cat.end_date_active,trunc(sysdate))
AND typ.ci_type_id = p_ci_type_id
AND cat.class_category = typ.resolution_category
AND cat.class_code_id = p_resolution_code_id;
SELECT type.auto_number_flag
FROM pa_ci_types_b type,
pa_control_items ci
WHERE ci.ci_id = p_ci_id
AND ci.ci_type_id = type.ci_type_id;
SELECT ROWID
FROM pa_control_items
WHERE project_id = p_project_id
AND ci_number = p_ci_number
AND ci_id <> p_ci_id
AND ci_type_id = p_ci_type_id;
SELECT cit.ci_type_class_code,
cit.approval_required_flag,
s.next_allowable_status_flag
FROM pa_control_items c,
pa_ci_types_b cit,
pa_project_statuses s
WHERE c.ci_id = p_ci_id
AND c.status_code = s.project_status_code
AND c.ci_type_id =cit.ci_type_id
AND s.status_type = 'CONTROL_ITEM';
select distinct resource_source_name party_name
from PA_PROJECT_PARTIES_V
where party_type <> 'ORGANIZATION'
and resource_party_id = c_owner_id
and project_id = c_project_id;
PA_DEBUG.set_curr_function(p_function => 'UPDATE_CHANGE_REQUEST', p_debug_mode => l_debug_mode);
savepoint UPDATE_CR_SVPT;
pa_debug.write(l_module, 'Start of Update Change Request', l_debug_level3);
/* Check if the user can update the item. This requires the user to be owner or to have project authority or
to have open UPDATE actions and status controls are satisfied. */
l_check_update_access := pa_ci_security_pkg.check_update_access(p_ci_id);
IF (l_check_update_access = 'F') THEN
PA_UTILS.ADD_MESSAGE('PA', 'PA_CI_NO_UPDATE_ACCESS');
pa_debug.write(l_module, 'After call to pa_ci_security_pkg.check_update_access', l_debug_level3);
/* Check for the status control: check whether the action CONTROL_ITEM_ALLOW_UPDATE is allowed on the current status of the issue. */
l_chk_status_ctrl := pa_control_items_utils.CheckCIActionAllowed('CONTROL_ITEM', l_curr_status_code, 'CONTROL_ITEM_ALLOW_UPDATE');
PA_UTILS.ADD_MESSAGE('PA', 'PA_CI_NO_ALLOW_UPDATE');
/* The control item will not be updateable if the current status has approval workflow attached. */
OPEN c_submit_status(l_curr_status_code);
l_sel_clause := ' SELECT ps.project_status_code ';
' and ps.project_status_code in (select project_status_code from pa_project_statuses where status_type = '||'''CONTROL_ITEM'''||
' and project_system_status_code in ( select next_allowable_status_code from pa_next_allow_statuses where status_code = '||
''''||l_curr_status_code||''''||')))'||
' OR '||
' ('||''''||l_next_allow_status_flag||''''||' = '||'''U'''||
' and ps.project_status_code in (select next_allowable_status_code from pa_next_allow_statuses where status_code = '||''''||
l_curr_status_code||''''||'))'||
' OR '||
' ('||''''||l_next_allow_status_flag||''''||' = '||'''A'''||
' and ps.project_status_code in (select project_status_code from pa_project_statuses where status_type = '||'''CONTROL_ITEM'''||
' and project_system_status_code in (select next_allowable_status_code from pa_next_allow_statuses where status_code = '||
''''||l_curr_system_status||''''||'))))'||
' AND ps.project_status_code not in (select wf_success_status_code from pa_project_statuses where status_type = '||
'''CONTROL_ITEM'''||' and wf_success_status_code is not null and wf_failure_status_code is not null)'||
' AND ps.project_status_code not in (select wf_failure_status_code from pa_project_statuses where status_type = '||
'''CONTROL_ITEM'''||' and wf_success_status_code is not null and wf_failure_status_code is not null)'||
' AND decode(ps.project_system_status_code, '||'''CI_CANCELED'''||
', nvl(pa_control_items_utils.CheckCIActionAllowed('||'''CONTROL_ITEM'''||', '||''''||l_curr_status_code||''''||', '||
'''CONTROL_ITEM_ALLOW_CANCEL'''||', null),'||'''N'''||' ),'||'''Y'''||' ) = '||'''Y'''||
' AND decode(ps.project_system_status_code,'||'''CI_WORKING'''||
' ,nvl(pa_control_items_utils.CheckCIActionAllowed('||'''CONTROL_ITEM'''||', '||''''||l_curr_status_code||''''||', '||
'''CONTROL_ITEM_ALLOW_REWORK'''||' ,null),'||'''N'''||' ),'||'''Y'''||' ) = '||'''Y'''||
' AND nvl(pa_control_items_utils.CheckCIActionAllowed('||'''CONTROL_ITEM'''||', '||''''||l_curr_status_code||''''||', '||
'''CONTROL_ITEM_ALLOW_UPDST'''||' ,null),'||'''N'''||' ) = '||'''Y'''||
' AND decode(ps.project_system_status_code,'||'''CI_DRAFT'''||
' ,decode('||''''||l_curr_system_status||''''||', '||'''CI_DRAFT'''||', '||
'''Y'''||' ,'||'''N'''||' ),'||'''Y'''||' ) = '||'''Y'''||
' AND ps.project_status_code = '||''''||p_ci_status_code||'''';
pa_ci_comments_pkg.insert_row(
p_ci_comment_id => l_ci_comment_id,
p_ci_id => p_ci_id,
p_type_code => 'CHANGE_OWNER',
p_comment_text => l_comment_text,
p_last_updated_by => fnd_global.user_id,
p_created_by => fnd_global.user_id,
p_creation_date => sysdate,
p_last_update_date => sysdate,
p_last_update_login => fnd_global.login_id,
p_ci_action_id => null);
/* Getting validated in pa_control_items_pub.update_control_item API. */
ELSIF p_owner_id IS NULL THEN
PA_UTILS.Add_Message( p_app_short_name => 'PA'
,p_msg_name => 'PA_CI_OWNER_NULL');
SELECT proj_element_id
INTO l_object_id
FROM PA_FIN_LATEST_PUB_TASKS_V
WHERE project_id = l_project_id
AND proj_element_id = p_object_id;
/* Getting validated in pa_control_items_pvt.update_control_item API. */
ELSIF p_price_currency_code IS NULL THEN
l_price_currency_code := p_price_currency_code;
pa_debug.write(l_module, 'before call to PA_CONTROL_ITEMS_PUB.UPDATE_CONTROL_ITEM', l_debug_level3);
PA_CONTROL_ITEMS_PUB.UPDATE_CONTROL_ITEM (
p_api_version => 1.0
,p_init_msg_list => fnd_api.g_false
,p_commit => FND_API.g_false
,p_validate_only => FND_API.g_false
,p_max_msg_count => FND_API.g_miss_num
,p_ci_id => p_ci_id
,p_ci_type_id => l_ci_type_id
,p_summary => l_summary
,p_status_code => l_ci_status_code
,p_owner_id => l_owner_id
,p_owner_name => null
,p_highlighted_flag => null
,p_progress_status_code => l_progress_status_code
,p_progress_as_of_date => l_progress_as_of_date
,p_classification_code => l_classification_code_id
,p_reason_code => l_reason_code_id
,p_record_version_number => l_record_version_number
,p_project_id => l_project_id
,p_object_type => l_object_type
,p_object_id => l_object_id
,p_object_name => null
,p_ci_number => l_ci_number
,p_date_required => l_date_required
,p_date_closed => l_date_closed
,p_closed_by_id => l_closed_by_id
,p_description => l_description
,p_status_overview => l_progress_overview
,p_resolution => l_resolution_comment
,p_resolution_code => l_resolution_code_id
,p_priority_code => l_priority_code
,p_effort_level_code => l_effort_level_code
,p_open_action_num => null
,p_price => l_price
,p_price_currency_code => l_price_currency_code
,p_source_type_code => l_source_type_code
,p_source_comment => l_source_comment
,p_source_number => l_source_number
,p_source_date_received => l_source_date_received
,p_source_organization => l_source_organization
,p_source_person => l_source_person
,p_attribute_category => l_attribute_category
,p_attribute1 => l_attribute1
,p_attribute2 => l_attribute2
,p_attribute3 => l_attribute3
,p_attribute4 => l_attribute4
,p_attribute5 => l_attribute5
,p_attribute6 => l_attribute6
,p_attribute7 => l_attribute7
,p_attribute8 => l_attribute8
,p_attribute9 => l_attribute9
,p_attribute10 => l_attribute10
,p_attribute11 => l_attribute11
,p_attribute12 => l_attribute12
,p_attribute13 => l_attribute13
,p_attribute14 => l_attribute14
,p_attribute15 => l_attribute15
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
pa_debug.write(l_module, 'after call to PA_CONTROL_ITEMS_PUB.UPDATE_CONTROL_ITEM : x_return_status = '||x_return_status, l_debug_level3);
/* call the insert table handlers of pa_obj_status_changes and pa_ci_comments here */
IF l_debug_mode = 'Y' THEN
pa_debug.write(l_module, 'before call to PA_CONTROL_ITEMS_UTILS.ADD_STATUS_CHANGE_COMMENT', l_debug_level3);
ROLLBACK TO UPDATE_CR_SVPT;
ROLLBACK TO UPDATE_CR_SVPT;
, p_procedure_name => 'UPDATE_CHANGE_REQUEST'
, p_error_text => x_msg_data);
END UPDATE_CHANGE_REQUEST;
PROCEDURE UPDATE_CHANGE_ORDER (
p_commit IN VARCHAR2 := FND_API.G_FALSE,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_api_version_number IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_ci_id IN NUMBER,
P_RECORD_VERSION_NUMBER IN NUMBER := G_PA_MISS_NUM,
P_SUMMARY IN VARCHAR2 := G_PA_MISS_CHAR,
P_DESCRIPTION IN VARCHAR2 := G_PA_MISS_CHAR,
P_OWNER_ID IN NUMBER := G_PA_MISS_NUM,
P_OWNER_COMMENT IN VARCHAR2 := G_PA_MISS_CHAR,
P_CLASSIFICATION_CODE IN NUMBER := G_PA_MISS_NUM,
P_REASON_CODE IN NUMBER := G_PA_MISS_NUM,
P_OBJECT_ID IN NUMBER := G_PA_MISS_NUM,
P_OBJECT_TYPE IN VARCHAR2 := G_PA_MISS_CHAR,
P_CI_NUMBER IN VARCHAR2 := G_PA_MISS_CHAR,
P_DATE_REQUIRED IN DATE := G_PA_MISS_DATE,
P_PRIORITY_CODE IN VARCHAR2 := G_PA_MISS_CHAR,
P_EFFORT_LEVEL_CODE IN VARCHAR2 := G_PA_MISS_CHAR,
P_PRICE IN NUMBER := G_PA_MISS_NUM,
P_PRICE_CURRENCY_CODE IN VARCHAR2 := G_PA_MISS_CHAR,
P_SOURCE_TYPE_CODE IN VARCHAR2 := G_PA_MISS_CHAR,
P_SOURCE_NUMBER IN VARCHAR2 := G_PA_MISS_CHAR,
P_SOURCE_COMMENT IN VARCHAR2 := G_PA_MISS_CHAR,
P_SOURCE_DATE_RECEIVED IN DATE := G_PA_MISS_DATE,
P_SOURCE_ORGANIZATION IN VARCHAR2 := G_PA_MISS_CHAR,
P_SOURCE_PERSON IN VARCHAR2 := G_PA_MISS_CHAR,
P_CI_STATUS_CODE IN VARCHAR2 := G_PA_MISS_CHAR,
P_STATUS_COMMENT IN VARCHAR2 := G_PA_MISS_CHAR,
P_PROGRESS_AS_OF_DATE IN DATE := G_PA_MISS_DATE,
P_PROGRESS_STATUS_CODE IN VARCHAR2 := G_PA_MISS_CHAR,
P_PROGRESS_OVERVIEW IN VARCHAR2 := G_PA_MISS_CHAR,
P_RESOLUTION_CODE IN VARCHAR2 := G_PA_MISS_CHAR,
P_RESOLUTION_COMMENT IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE_CATEGORY IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE1 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE2 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE3 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE4 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE5 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE6 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE7 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE8 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE9 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE10 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE11 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE12 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE13 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE14 IN VARCHAR2 := G_PA_MISS_CHAR,
P_ATTRIBUTE15 IN VARCHAR2 := G_PA_MISS_CHAR
)
IS
l_data VARCHAR2(2000);
l_check_update_access VARCHAR2(1) := 'F';
l_module VARCHAR2(100) := 'PA_CONTROL_API_PUB.UPDATE_CHANGE_ORDER';
SELECT *
FROM pa_control_items
WHERE ci_id = p_ci_id;
SELECT enable_wf_flag, wf_success_status_code, wf_failure_status_code
FROM pa_project_statuses
WHERE project_status_code = p_curr_status_code;
SELECT lookup_code
FROM pa_lookups
WHERE lookup_type = p_lookup_type
AND trunc(sysdate) BETWEEN start_date_active AND nvl(end_date_active, trunc(sysdate))
AND enabled_flag = 'Y'
AND lookup_code = p_lookup_code;
SELECT project_status_code
FROM pa_project_statuses
WHERE status_type = p_status_type
AND project_status_code = p_project_status_code
AND trunc(sysdate) BETWEEN start_date_active AND nvl(end_date_active, trunc(sysdate));
SELECT cat.class_code_id
FROM pa_class_codes cat,
pa_ci_types_b typ
WHERE trunc(sysdate) between cat.start_date_active and nvl(cat.end_date_active,trunc(sysdate))
AND typ.ci_type_id = p_ci_type_id
AND cat.class_category = typ.classification_category
AND cat.class_code_id = p_class_code_id;
SELECT cat.class_code_id
FROM pa_class_codes cat,
pa_ci_types_b typ
WHERE trunc(sysdate) between cat.start_date_active and nvl(cat.end_date_active,trunc(sysdate))
AND typ.ci_type_id = p_ci_type_id
AND cat.class_category = typ.reason_category
AND cat.class_code_id = p_reason_code_id;
SELECT cat.class_code_id
FROM pa_class_codes cat,
pa_ci_types_b typ
WHERE trunc(sysdate) between cat.start_date_active and nvl(cat.end_date_active,trunc(sysdate))
AND typ.ci_type_id = p_ci_type_id
AND cat.class_category = typ.resolution_category
AND cat.class_code_id = p_resolution_code_id;
SELECT type.auto_number_flag
FROM pa_ci_types_b type,
pa_control_items ci
WHERE ci.ci_id = p_ci_id
AND ci.ci_type_id = type.ci_type_id;
SELECT ROWID
FROM pa_control_items
WHERE project_id = p_project_id
AND ci_number = p_ci_number
AND ci_id <> p_ci_id
AND ci_type_id = p_ci_type_id;
SELECT cit.ci_type_class_code,
cit.approval_required_flag,
s.next_allowable_status_flag
FROM pa_control_items c,
pa_ci_types_b cit,
pa_project_statuses s
WHERE c.ci_id = p_ci_id
AND c.status_code = s.project_status_code
AND c.ci_type_id =cit.ci_type_id
AND s.status_type = 'CONTROL_ITEM';
select distinct resource_source_name party_name
from PA_PROJECT_PARTIES_V
where party_type <> 'ORGANIZATION'
and resource_party_id = c_owner_id
and project_id = c_project_id;
PA_DEBUG.set_curr_function(p_function => 'UPDATE_CHANGE_ORDER', p_debug_mode => l_debug_mode);
savepoint UPDATE_CO_SVPT;
pa_debug.write(l_module, 'Start of Update Chaneg Order', l_debug_level3);
/* Check if the user can update the item. This requires the user to be owner or to have project authority or
to have open UPDATE actions and status controls are satisfied. */
l_check_update_access := pa_ci_security_pkg.check_update_access(p_ci_id);
IF (l_check_update_access = 'F') THEN
PA_UTILS.ADD_MESSAGE('PA', 'PA_CI_NO_UPDATE_ACCESS');
pa_debug.write(l_module, 'After call to pa_ci_security_pkg.check_update_access', l_debug_level3);
/* Check for the status control: check whether the action CONTROL_ITEM_ALLOW_UPDATE is allowed on the current status of the issue. */
l_chk_status_ctrl := pa_control_items_utils.CheckCIActionAllowed('CONTROL_ITEM', l_curr_status_code, 'CONTROL_ITEM_ALLOW_UPDATE');
PA_UTILS.ADD_MESSAGE('PA', 'PA_CI_NO_ALLOW_UPDATE');
/* The control item will not be updateable if the current status has approval workflow attached. */
OPEN c_submit_status(l_curr_status_code);
l_sel_clause := ' SELECT ps.project_status_code ';
' and ps.project_status_code in (select project_status_code from pa_project_statuses where status_type = '||'''CONTROL_ITEM'''||
' and project_system_status_code in ( select next_allowable_status_code from pa_next_allow_statuses where status_code = '||
''''||l_curr_status_code||''''||')))'||
' OR '||
' ('||''''||l_next_allow_status_flag||''''||' = '||'''U'''||
' and ps.project_status_code in (select next_allowable_status_code from pa_next_allow_statuses where status_code = '||''''||
l_curr_status_code||''''||'))'||
' OR '||
' ('||''''||l_next_allow_status_flag||''''||' = '||'''A'''||
' and ps.project_status_code in (select project_status_code from pa_project_statuses where status_type = '||'''CONTROL_ITEM'''||
' and project_system_status_code in (select next_allowable_status_code from pa_next_allow_statuses where status_code = '||
''''||l_curr_system_status||''''||'))))'||
' AND ps.project_status_code not in (select wf_success_status_code from pa_project_statuses where status_type = '||
'''CONTROL_ITEM'''||' and wf_success_status_code is not null and wf_failure_status_code is not null)'||
' AND ps.project_status_code not in (select wf_failure_status_code from pa_project_statuses where status_type = '||
'''CONTROL_ITEM'''||' and wf_success_status_code is not null and wf_failure_status_code is not null)'||
' AND decode(ps.project_system_status_code, '||'''CI_CANCELED'''||
', nvl(pa_control_items_utils.CheckCIActionAllowed('||'''CONTROL_ITEM'''||', '||''''||l_curr_status_code||''''||', '||
'''CONTROL_ITEM_ALLOW_CANCEL'''||', null),'||'''N'''||' ),'||'''Y'''||' ) = '||'''Y'''||
' AND decode(ps.project_system_status_code,'||'''CI_WORKING'''||
' ,nvl(pa_control_items_utils.CheckCIActionAllowed('||'''CONTROL_ITEM'''||', '||''''||l_curr_status_code||''''||', '||
'''CONTROL_ITEM_ALLOW_REWORK'''||' ,null),'||'''N'''||' ),'||'''Y'''||' ) = '||'''Y'''||
' AND nvl(pa_control_items_utils.CheckCIActionAllowed('||'''CONTROL_ITEM'''||', '||''''||l_curr_status_code||''''||', '||
'''CONTROL_ITEM_ALLOW_UPDST'''||' ,null),'||'''N'''||' ) = '||'''Y'''||
' AND decode(ps.project_system_status_code,'||'''CI_DRAFT'''||
' ,decode('||''''||l_curr_system_status||''''||', '||'''CI_DRAFT'''||', '||
'''Y'''||' ,'||'''N'''||' ),'||'''Y'''||' ) = '||'''Y'''||
' AND ps.project_status_code = '||''''||p_ci_status_code||'''';
pa_ci_comments_pkg.insert_row(
p_ci_comment_id => l_ci_comment_id,
p_ci_id => p_ci_id,
p_type_code => 'CHANGE_OWNER',
p_comment_text => l_comment_text,
p_last_updated_by => fnd_global.user_id,
p_created_by => fnd_global.user_id,
p_creation_date => sysdate,
p_last_update_date => sysdate,
p_last_update_login => fnd_global.login_id,
p_ci_action_id => null);
/* Getting validated in pa_control_items_pub.update_control_item API. */
ELSIF p_owner_id IS NULL THEN
PA_UTILS.Add_Message( p_app_short_name => 'PA'
,p_msg_name => 'PA_CI_OWNER_NULL');
SELECT proj_element_id
INTO l_object_id
FROM PA_FIN_LATEST_PUB_TASKS_V
WHERE project_id = l_project_id
AND proj_element_id = p_object_id;
/* Getting validated in pa_control_items_pvt.update_control_item API. */
ELSIF p_price_currency_code IS NULL THEN
l_price_currency_code := p_price_currency_code;
pa_debug.write(l_module, 'before call to PA_CONTROL_ITEMS_PUB.UPDATE_CONTROL_ITEM', l_debug_level3);
PA_CONTROL_ITEMS_PUB.UPDATE_CONTROL_ITEM (
p_api_version => 1.0
,p_init_msg_list => fnd_api.g_false
,p_commit => FND_API.g_false
,p_validate_only => FND_API.g_false
,p_max_msg_count => FND_API.g_miss_num
,p_ci_id => p_ci_id
,p_ci_type_id => l_ci_type_id
,p_summary => l_summary
,p_status_code => l_ci_status_code
,p_owner_id => l_owner_id
,p_owner_name => null
,p_highlighted_flag => null
,p_progress_status_code => l_progress_status_code
,p_progress_as_of_date => l_progress_as_of_date
,p_classification_code => l_classification_code_id
,p_reason_code => l_reason_code_id
,p_record_version_number => l_record_version_number
,p_project_id => l_project_id
,p_object_type => l_object_type
,p_object_id => l_object_id
,p_object_name => null
,p_ci_number => l_ci_number
,p_date_required => l_date_required
,p_date_closed => l_date_closed
,p_closed_by_id => l_closed_by_id
,p_description => l_description
,p_status_overview => l_progress_overview
,p_resolution => l_resolution_comment
,p_resolution_code => l_resolution_code_id
,p_priority_code => l_priority_code
,p_effort_level_code => l_effort_level_code
,p_open_action_num => null
,p_price => l_price
,p_price_currency_code => l_price_currency_code
,p_source_type_code => l_source_type_code
,p_source_comment => l_source_comment
,p_source_number => l_source_number
,p_source_date_received => l_source_date_received
,p_source_organization => l_source_organization
,p_source_person => l_source_person
,p_attribute_category => l_attribute_category
,p_attribute1 => l_attribute1
,p_attribute2 => l_attribute2
,p_attribute3 => l_attribute3
,p_attribute4 => l_attribute4
,p_attribute5 => l_attribute5
,p_attribute6 => l_attribute6
,p_attribute7 => l_attribute7
,p_attribute8 => l_attribute8
,p_attribute9 => l_attribute9
,p_attribute10 => l_attribute10
,p_attribute11 => l_attribute11
,p_attribute12 => l_attribute12
,p_attribute13 => l_attribute13
,p_attribute14 => l_attribute14
,p_attribute15 => l_attribute15
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
pa_debug.write(l_module, 'after call to PA_CONTROL_ITEMS_PUB.UPDATE_CONTROL_ITEM : x_return_status = '||x_return_status, l_debug_level3);
/* call the insert table handlers of pa_obj_status_changes and pa_ci_comments here */
IF l_debug_mode = 'Y' THEN
pa_debug.write(l_module, 'before call to PA_CONTROL_ITEMS_UTILS.ADD_STATUS_CHANGE_COMMENT', l_debug_level3);
ROLLBACK TO UPDATE_CO_SVPT;
ROLLBACK TO UPDATE_CO_SVPT;
, p_procedure_name => 'UPDATE_CHANGE_ORDER'
, p_error_text => x_msg_data);
END UPDATE_CHANGE_ORDER;