[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select project_system_status_code
From PA_PROJECT_STATUSES
Where project_status_code = p_status_code;
Select ci_type_class_code, auto_number_flag,
start_date_active,end_date_active
From PA_CI_TYPES_B
Where ci_type_id = p_ci_type_id;
select ROWID
into l_rowid
from fnd_currencies_tl
where currency_code = p_price_currency_code
AND language = USERENV('LANG');
PA_CONTROL_ITEMS_PKG.INSERT_ROW (
p_ci_type_id
,p_summary
,p_status_code
,p_owner_id
,p_highlighted_flag
,NVL(p_progress_status_code, 'PROGRESS_STAT_ON_TRACK')
,NVL(p_progress_as_of_date,sysdate)
,p_classification_code
,p_reason_code
,p_project_id
-- ,sysdate
,p_last_modified_by_id
,p_object_type
,p_object_id
,l_ci_number_char
,p_date_required
,p_date_closed
,p_closed_by_id
,p_description
,p_status_overview
,p_resolution
,p_resolution_code
,p_priority_code
,p_effort_level_code
,nvl(p_open_action_num,0)
,p_price
,p_price_currency_code
,p_source_type_code
,p_source_comment
,p_source_number
,p_source_date_received
,p_source_organization
,p_source_person
,p_attribute_category
,p_attribute1
,p_attribute2
,p_attribute3
,p_attribute4
,p_attribute5
,p_attribute6
,p_attribute7
,p_attribute8
,p_attribute9
,p_attribute10
,p_attribute11
,p_attribute12
,p_attribute13
,p_attribute14
,p_attribute15
-- start: 26-Jun-2009 cklee Modified for the Bug# 8633676
,p_PCO_STATUS_CODE
,p_APPROVAL_TYPE_CODE
,p_LOCKED_FLAG
-- end: 26-Jun-2009 cklee Modified for the Bug# 8633676
,p_Version_number
,p_Current_Version_flag
,p_Version_Comments
,p_Original_ci_id
,p_Source_ci_id
,px_ci_id
,x_return_status
,x_msg_count
,x_msg_data
);
procedure UPDATE_CONTROL_ITEM (
p_api_version IN NUMBER := 1.0
,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
,p_commit IN VARCHAR2 := FND_API.g_false
,p_validate_only IN VARCHAR2 := FND_API.g_true
,p_max_msg_count IN NUMBER := FND_API.g_miss_num
,p_ci_id IN NUMBER
,p_ci_type_id IN NUMBER
,p_summary IN VARCHAR2
,p_status_code IN VARCHAR2 := NULL
,p_owner_id IN NUMBER
,p_highlighted_flag IN VARCHAR2 := 'N'
,p_progress_status_code IN VARCHAR2
,p_progress_as_of_date IN DATE := NULL
,p_classification_code IN NUMBER
,p_reason_code IN NUMBER
,p_record_version_number IN NUMBER
,p_project_id IN NUMBER
,p_last_modified_by_id IN NUMBER
:= NVL(PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id ), fnd_global.user_id) -- 26-Jun-2009 cklee Modified for the Bug# 8633676
,p_object_type IN VARCHAR2 := NULL
,p_object_id IN NUMBER := NULL
,p_ci_number IN VARCHAR2 := NULL
,p_date_required IN DATE := NULL
,p_date_closed IN DATE := NULL
,p_closed_by_id IN NUMBER := NULL
,p_description IN VARCHAR2 := NULL
,p_status_overview IN VARCHAR2 := NULL
,p_resolution IN VARCHAR2 := NULL
,p_resolution_code IN NUMBER := NULL
,p_priority_code IN VARCHAR2 := NULL
,p_effort_level_code IN VARCHAR2 := NULL
,p_open_action_num IN NUMBER := NULL
,p_price IN NUMBER := NULL
,p_price_currency_code IN VARCHAR2 := NULL
,p_source_type_code IN VARCHAR2 := NULL
,p_source_comment IN VARCHAR2 := NULL
,p_source_number IN VARCHAR2 := NULL
,p_source_date_received IN DATE := NULL
,p_source_organization IN VARCHAR2 := NULL
,p_source_person IN VARCHAR2 := NULL
,p_attribute_category IN VARCHAR2 := NULL
,p_attribute1 IN VARCHAR2 := NULL
,p_attribute2 IN VARCHAR2 := NULL
,p_attribute3 IN VARCHAR2 := NULL
,p_attribute4 IN VARCHAR2 := NULL
,p_attribute5 IN VARCHAR2 := NULL
,p_attribute6 IN VARCHAR2 := NULL
,p_attribute7 IN VARCHAR2 := NULL
,p_attribute8 IN VARCHAR2 := NULL
,p_attribute9 IN VARCHAR2 := NULL
,p_attribute10 IN VARCHAR2 := NULL
,p_attribute11 IN VARCHAR2 := NULL
,p_attribute12 IN VARCHAR2 := NULL
,p_attribute13 IN VARCHAR2 := NULL
,p_attribute14 IN VARCHAR2 := NULL
,p_attribute15 IN VARCHAR2 := NULL
-- start: 26-Jun-2009 cklee Modified for the Bug# 8633676
,p_PCO_STATUS_CODE IN VARCHAR2 := NULL
,p_APPROVAL_TYPE_CODE IN VARCHAR2 := NULL
,p_LOCKED_FLAG IN VARCHAR2 := 'N'
-- end: 26-Jun-2009 cklee Modified for the Bug# 8633676
,p_Version_number IN number := null
,p_Current_Version_flag IN varchar2 := 'Y'
,p_Version_Comments IN varchar2 := NULL
,p_Original_ci_id IN number := NULL
,p_Source_ci_id IN number := NULL
,p_change_approver IN varchar2 := NULL
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) is
CURSOR curr_row is
SELECT *
FROM pa_control_items
WHERE ci_id = p_ci_id;
cursor C is 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 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;
PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.Update_Control_Item');
SAVEPOINT update_control_item;
select ROWID
into l_ROWID
from fnd_currencies_tl
where currency_code = p_price_currency_code
AND language = USERENV('LANG');
PA_CONTROL_ITEMS_PKG.UPDATE_ROW(
p_ci_id
,p_ci_type_id
,p_summary
,l_new_status_code
,p_owner_id
,p_highlighted_flag
,p_progress_status_code
,l_as_of_date --p_progress_as_of_date
,p_classification_code
,p_reason_code
,p_record_version_number
,p_project_id
,p_last_modified_by_id
,p_object_type
,p_object_id
,l_ci_number --p_ci_number
,p_date_required
,p_date_closed
,p_closed_by_id
,p_description
,p_status_overview
,p_resolution
,p_resolution_code
,p_priority_code
,p_effort_level_code
,p_open_action_num
,p_price
,p_price_currency_code
,p_source_type_code
,p_source_comment
,p_source_number
,p_source_date_received
,p_source_organization
,p_source_person
,p_attribute_category
,p_attribute1
,p_attribute2
,p_attribute3
,p_attribute4
,p_attribute5
,p_attribute6
,p_attribute7
,p_attribute8
,p_attribute9
,p_attribute10
,p_attribute11
,p_attribute12
,p_attribute13
,p_attribute14
,p_attribute15
-- start: 26-Jun-2009 cklee Modified for the Bug# 8633676
,p_PCO_STATUS_CODE
,p_APPROVAL_TYPE_CODE
,l_locked_flag -- p_LOCKED_FLAG
-- end: 26-Jun-2009 cklee Modified for the Bug# 8633676
,p_Version_number
,p_Current_Version_flag
,p_Version_Comments
,p_Original_ci_id
,p_Source_ci_id
,p_change_approver
,x_return_status
,x_msg_count
,x_msg_data
);
ROLLBACK TO update_control_item;
FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.update_control_item'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
end UPDATE_CONTROL_ITEM;
procedure DELETE_CONTROL_ITEM (
p_api_version IN NUMBER := 1.0
,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
,p_commit IN VARCHAR2 := FND_API.g_false
,p_validate_only IN VARCHAR2 := FND_API.g_true
,p_max_msg_count IN NUMBER := FND_API.g_miss_num
,p_ci_id IN NUMBER
,p_record_version_number IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) is
l_status_code pa_project_statuses.project_system_status_code%type;
select pps.project_system_status_code --status_code
from pa_control_items ci
,pa_project_statuses pps
where ci.ci_id = p_ci_id
and ci.status_code = pps.project_status_code;
PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.Delete_Control_Item');
SAVEPOINT delete_control_item;
pa_ci_actions_pvt.delete_all_actions(p_validate_only => 'F',
p_init_msg_list => 'F',
p_ci_id => p_ci_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
pa_ci_impacts_util.delete_All_impacts(p_validate_only => 'F',
p_init_msg_list => 'F',
p_ci_id => p_ci_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
delete_all_related_items (p_validate_only => 'F',
p_init_msg_list => 'F',
p_ci_id => p_ci_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
delete_all_included_crs (p_validate_only => 'F',
p_init_msg_list => 'F',
p_ci_id => p_ci_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
pa_ci_doc_attach_pkg.delete_all_attachments (p_validate_only => 'F',
p_init_msg_list => 'F',
p_ci_id => p_ci_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
PA_CONTROL_ITEMS_PKG.DELETE_ROW(
p_ci_id
,p_record_version_number
,x_return_status
,x_msg_count
,x_msg_data
);
ROLLBACK TO delete_control_item;
FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.delete_control_item'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
end DELETE_CONTROL_ITEM;
procedure DELETE_ALL_CONTROL_ITEMS(
p_api_version IN NUMBER := 1.0
,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
,p_commit IN VARCHAR2 := FND_API.g_false
,p_validate_only IN VARCHAR2 := FND_API.g_true
,p_max_msg_count IN NUMBER := FND_API.g_miss_num
,p_project_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) is
l_msg_index_out NUMBER;
PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.Delete_ALL_Control_Items');
SAVEPOINT delete_all_control_items;
FOR ci_rec IN( SELECT ci_id, record_version_number
FROM pa_control_items
WHERE project_id = p_project_id ) LOOP
DELETE_CONTROL_ITEM(
p_api_version
,'F'
,'F'
,'F'
,p_max_msg_count
,ci_rec.ci_id
,ci_rec.record_version_number
,x_return_status
,x_msg_count
,x_msg_data );
ROLLBACK TO delete_all_control_items;
FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.delete_all_control_items'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
end DELETE_ALL_CONTROL_ITEMS;
SELECT * FROM pa_control_items
WHERE ci_id = p_ci_id_from;
SELECT 'Y'
FROM pa_ci_types_b pctb, pa_class_codes pcc
WHERE pctb.ci_type_id = p_ci_type_id
AND pctb.classification_category = pcc.class_category
AND pcc.class_code = p_class_code;
SELECT class_code_id
FROM pa_class_codes pcc,pa_ci_types_b pctb
WHERE pctb.ci_type_id = p_ci_type_id
AND pctb.classification_category = pcc.class_category
AND pcc.class_code in (select pcc1.class_code
from pa_class_codes pcc1
where pcc1.class_code_id = p_class_code);
SELECT class_code_id
FROM pa_class_codes pcc,pa_ci_types_b pctb
WHERE pctb.ci_type_id = p_ci_type_id
AND pctb.reason_category = pcc.class_category
AND pcc.class_code in (select pcc1.class_code
from pa_class_codes pcc1
where pcc1.class_code_id = p_reason);
SELECT distinct ps.project_status_code FROM pa_ci_statuses_v pc,pa_project_statuses ps where ps.project_status_code=pc.project_status_code
and ps.project_system_status_code =pc.project_system_status_code and ci_type_id = p_ci_type_id
and ps.starting_status_flag = 'Y'
and ci_type_id in (select ci_type_id from pa_ci_types_v ) and ps.project_system_status_code = l_code
;
SELECT 'Y'
FROM pa_ci_types_b pctb, pa_class_codes pcc
WHERE pctb.ci_type_id = p_ci_type_id
AND pctb.reason_category = pcc.class_category
AND pcc.class_code = p_reason;
SELECT pci.ci_type_id, pctb.ci_type_class_code,
pci.project_id, pps.project_system_status_code
FROM pa_control_items pci, pa_ci_types_b pctb, pa_project_statuses pps
WHERE pci.ci_id = l_ci_id
and pci.ci_type_id = pctb.ci_type_id
and pci.status_code = pps.project_status_code(+);
PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
p_user_id => fnd_global.user_id,
p_object_type_from => 'PA_CONTROL_ITEMS',
p_object_id_from1 => to_char(p_from_ci_id),
p_object_id_from2 => NULL,
p_object_id_from3 => NULL,
p_object_id_from4 => NULL,
p_object_id_from5 => NULL,
p_object_type_to => 'PA_CONTROL_ITEMS',
p_object_id_to1 => to_char(p_to_ci_id),
p_object_id_to2 => NULL,
p_object_id_to3 => NULL,
p_object_id_to4 => NULL,
p_object_id_to5 => NULL,
p_relationship_type => l_relationship_type,
p_relationship_subtype => NULL,
p_lag_day => NULL,
p_imported_lag => NULL,
p_priority => NULL,
p_pm_product_code => NULL,
x_object_relationship_id => x_relationship_id,
x_return_status => x_return_status);
SELECT record_version_number
INTO l_record_version_number
FROM PA_CONTROL_ITEMS
WHERE ci_id = p_to_ci_id;
procedure UPDATE_NUMBER_OF_ACTIONS (
p_api_version IN NUMBER := 1.0
,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
,p_commit IN VARCHAR2 := FND_API.g_false
,p_validate_only IN VARCHAR2 := FND_API.g_true
,p_max_msg_count IN NUMBER := FND_API.g_miss_num
,p_ci_id IN NUMBER
,p_num_of_actions IN NUMBER
,p_record_version_number IN NUMBER
,x_num_of_actions OUT NOCOPY NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_last_updated_by in NUMBER default fnd_global.user_id --Added the parameter for bug# 3877985
,p_last_update_date in DATE default sysdate --Added the parameter for bug# 3877985
,p_last_update_login in NUMBER default fnd_global.user_id --Added the parameter for bug# 3877985
)IS
l_nof_actions NUMBER(15) := 0;
SELECT * FROM pa_control_items
WHERE ci_id = p_ci_id;
PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.UPDATE_NUMBER_OF_ACTIONS');
SAVEPOINT UPDATE_NUMBER_OF_ACTIONS;
PA_CONTROL_ITEMS_PKG.UPDATE_ROW(
p_ci_id
,cp.ci_type_id
,cp.summary
,cp.status_code
,cp.owner_id
,cp.highlighted_flag
,cp.progress_status_code
,cp.progress_as_of_date
,cp.classification_code_id
,cp.reason_code_id
,p_record_version_number
,cp.project_id
-- start: 26-Jun-2009 cklee Modified for the Bug# 8633676
-- ,cp.last_modified_by_id
,NVL(PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id ), fnd_global.user_id)
-- end: 26-Jun-2009 cklee Modified for the Bug# 8633676
,cp.object_type
,cp.object_id
,cp.ci_number
,cp.date_required
,cp.date_closed
,cp.closed_by_id
,cp.description
,cp.status_overview
,cp.resolution
,cp.resolution_code_id
,cp.priority_code
,cp.effort_level_code
,l_nof_actions --open_action_num
,cp.price
,cp.price_currency_code
,cp.source_type_code
,cp.source_comment
,cp.source_number
,cp.source_date_received
,cp.source_organization
,cp.source_person
,cp.attribute_category
,cp.attribute1
,cp.attribute2
,cp.attribute3
,cp.attribute4
,cp.attribute5
,cp.attribute6
,cp.attribute7
,cp.attribute8
,cp.attribute9
,cp.attribute10
,cp.attribute11
,cp.attribute12
,cp.attribute13
,cp.attribute14
,cp.attribute15
-- start: 26-Jun-2009 cklee Modified for the Bug# 8633676
,cp.PCO_STATUS_CODE
,cp.APPROVAL_TYPE_CODE
,cp.LOCKED_FLAG
-- end: 26-Jun-2009 cklee Modified for the Bug# 8633676
,cp.Version_number
,cp.Current_Version_flag
,cp.Version_Comments
,cp.Original_ci_id
,cp.Source_ci_id
,cp.change_approver
,x_return_status
,x_msg_count
,x_msg_data
,p_last_updated_by --Added for bug# 3877985
,p_last_update_date --Added for bug# 3877985
,p_last_update_login --Added for bug# 3877985
);
ROLLBACK TO UPDATE_NUMBER_OF_ACTIONS;
FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.UPDATE_NUMBER_OF_ACTIONS'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
end UPDATE_NUMBER_OF_ACTIONS;
procedure UPDATE_CONTROL_ITEM_STATUS (
p_api_version IN NUMBER := 1.0
,p_init_msg_list IN VARCHAR2 := fnd_api.g_true
,p_commit IN VARCHAR2 := FND_API.g_false
,p_validate_only IN VARCHAR2 := FND_API.g_true
,p_max_msg_count IN NUMBER := FND_API.g_miss_num
,p_ci_id IN NUMBER
,p_status_code IN VARCHAR2
,p_record_version_number IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
API_ERROR EXCEPTION;
SELECT * FROM pa_control_items
WHERE ci_id = p_ci_id;
Select type.auto_number_flag, type.ci_type_id,type.ci_type_class_code
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;
PA_DEBUG.init_err_stack('PA_CONTROL_ITEMS_PVT.UPDATE_CONTROL_ITEM_STATUS');
SAVEPOINT UPDATE_CONTROL_ITEM_STATUS;
PA_CONTROL_ITEMS_PKG.UPDATE_ROW(
p_ci_id
,cp.ci_type_id
,cp.summary
,p_status_code
,cp.owner_id
,cp.highlighted_flag
,cp.progress_status_code
,cp.progress_as_of_date
,cp.classification_code_id
,cp.reason_code_id
,p_record_version_number
,cp.project_id
-- start: 26-Jun-2009 cklee Modified for the Bug# 8633676
-- ,PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id ) --cp.last_modified_by_id
,NVL(PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id ), fnd_global.user_id)
-- end: 26-Jun-2009 cklee Modified for the Bug# 8633676
,cp.object_type
,cp.object_id
,l_ci_number_char --cp.ci_number
,cp.date_required
,l_closed_date --cp.date_closed
,l_closed_by --cp.closed_by_id
,cp.description
,cp.status_overview
,cp.resolution
,cp.resolution_code_id
,cp.priority_code
,cp.effort_level_code
,cp.open_action_num
,cp.price
,cp.price_currency_code
,cp.source_type_code
,cp.source_comment
,cp.source_number
,cp.source_date_received
,cp.source_organization
,cp.source_person
,cp.attribute_category
,cp.attribute1
,cp.attribute2
,cp.attribute3
,cp.attribute4
,cp.attribute5
,cp.attribute6
,cp.attribute7
,cp.attribute8
,cp.attribute9
,cp.attribute10
,cp.attribute11
,cp.attribute12
,cp.attribute13
,cp.attribute14
,cp.attribute15
-- start: 26-Jun-2009 cklee Modified for the Bug# 8633676
,cp.PCO_STATUS_CODE
,cp.APPROVAL_TYPE_CODE
,cp.LOCKED_FLAG
-- end: 26-Jun-2009 cklee Modified for the Bug# 8633676
,cp.Version_number
,cp.Current_Version_flag
,cp.Version_Comments
,cp.Original_ci_id
,cp.Source_ci_id
,cp.change_approver
,x_return_status
,x_msg_count
,x_msg_data
);
ROLLBACK TO UPDATE_CONTROL_ITEM_STATUS;
FND_MSG_PUB.add_exc_msg ( p_pkg_name => 'PA_CONTROL_ITEMS_PVT.UPDATE_CONTROL_ITEM_STATUS'
,p_procedure_name => PA_DEBUG.G_Err_Stack );
end UPDATE_CONTROL_ITEM_STATUS;
cursor C is select ROWID from PA_SYSTEM_NUMBERS
where object1_pk1_value = p_project_id
and nvl(object2_pk1_value,0) = nvl(p_ci_type_id,0)
and object1_type = p_object_type
and object2_type = p_ci_type_code;
cursor C is select ROWID from PA_CONTROL_ITEMS
where project_id = p_project_id
and ci_number = p_ci_number
and ci_type_id = p_ci_type_id;
SELECT meaning
INTO l_meaning
FROM pa_lookups
WHERE lookup_type = p_lookup_type
AND lookup_code = p_lookup_code;
SELECT obj.object_id_to1 included_ci_id
,ci.record_version_number record_version_number
FROM pa_object_relationships obj, pa_control_items ci
WHERE obj.object_type_from = 'PA_CONTROL_ITEMS'
AND obj.object_type_to = 'PA_CONTROL_ITEMS'
AND obj.relationship_type = 'CI_INCLUDED_ITEM'
AND obj.object_id_from1 = p_ci_id
AND obj.object_id_to1 = ci.ci_id;
PA_OBJECT_RELATIONSHIPS_PKG.INSERT_ROW(
p_user_id => fnd_global.user_id,
p_object_type_from => 'PA_CONTROL_ITEMS',
p_object_id_from1 => to_char(p_ci_id),
p_object_id_from2 => NULL,
p_object_id_from3 => NULL,
p_object_id_from4 => NULL,
p_object_id_from5 => NULL,
p_object_type_to => 'PA_CONTROL_ITEMS',
p_object_id_to1 => to_char(p_related_ci_id),
p_object_id_to2 => NULL,
p_object_id_to3 => NULL,
p_object_id_to4 => NULL,
p_object_id_to5 => NULL,
p_relationship_type => 'CI_REFERENCED_ITEM',
p_relationship_subtype => NULL,
p_lag_day => NULL,
p_imported_lag => NULL,
p_priority => NULL,
p_pm_product_code => NULL,
x_object_relationship_id => l_object_relationship_id,
x_return_status => x_return_status);
PROCEDURE delete_related_item (
p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
p_commit IN VARCHAR2 := FND_API.g_false,
p_validate_only IN VARCHAR2 := FND_API.g_true,
p_max_msg_count IN NUMBER := FND_API.g_miss_num,
p_ci_id IN NUMBER,
p_related_ci_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_object_relationship_id NUMBER;
pa_debug.set_err_stack ('PA_CONTROL_ITEMS_PVT.DELETE_RELATED_ITEM');
SAVEPOINT delete_related_item;
SELECT object_relationship_id, record_version_number
INTO l_object_relationship_id, l_record_version_number
FROM pa_object_relationships
WHERE object_type_from = 'PA_CONTROL_ITEMS'
AND relationship_type = 'CI_REFERENCED_ITEM'
AND object_type_to = 'PA_CONTROL_ITEMS'
AND ( ( object_id_to1 = p_related_ci_id
AND object_id_from1 = p_ci_id)
OR ( object_id_to1 = p_ci_id
AND object_id_from1 = p_related_ci_id));
pa_object_relationships_pkg.delete_row(
p_object_relationship_id => l_object_relationship_id,
p_object_type_from => 'PA_CONTROL_ITEMS',
p_object_id_from1 => to_char(p_ci_id),
p_object_id_from2 => NULL,
p_object_id_from3 => NULL,
p_object_id_from4 => NULL,
p_object_id_from5 => NULL,
p_object_type_to => 'PA_CONTROL_ITEMS',
p_object_id_to1 => to_char(p_related_ci_id),
p_object_id_to2 => NULL,
p_object_id_to3 => NULL,
p_object_id_to4 => NULL,
p_object_id_to5 => NULL,
p_pm_product_code => NULL,
p_record_version_number => l_record_version_number,
x_return_status => x_return_status);
ROLLBACK TO delete_related_item;
ROLLBACK TO delete_related_item;
p_procedure_name => 'DELETE_RELATED_ITEM',
p_error_text => SUBSTRB(SQLERRM,1,240));
END delete_related_item;
PROCEDURE delete_all_related_items (
p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
p_commit IN VARCHAR2 := FND_API.g_false,
p_validate_only IN VARCHAR2 := FND_API.g_true,
p_max_msg_count IN NUMBER := FND_API.g_miss_num,
p_ci_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
CURSOR items_c IS
SELECT object_id_to1 related_ci_id
FROM pa_object_relationships
WHERE object_type_from = 'PA_CONTROL_ITEMS'
AND object_type_to = 'PA_CONTROL_ITEMS'
AND relationship_type = 'CI_REFERENCED_ITEM'
AND object_id_from1 = p_ci_id
UNION ALL
SELECT object_id_from1 related_ci_id
FROM pa_object_relationships
WHERE object_type_from = 'PA_CONTROL_ITEMS'
AND object_type_to = 'PA_CONTROL_ITEMS'
AND relationship_type = 'CI_REFERENCED_ITEM'
AND object_id_to1 = p_ci_id;
pa_debug.set_err_stack ('PA_CONTROL_ITEMS_PVT.DELETE_ALL_RELATED_ITEMS');
SAVEPOINT delete_all_related_items;
delete_related_item(
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 => p_max_msg_count,
p_ci_id => p_ci_id,
p_related_ci_id => cur.related_ci_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
ROLLBACK TO delete_all_related_items;
ROLLBACK TO delete_all_related_items;
p_procedure_name => 'DELETE_ALL_RELATED_ITEMS',
p_error_text => SUBSTRB(SQLERRM,1,240));
END delete_all_related_items;
PROCEDURE delete_all_included_crs (
p_api_version IN NUMBER := 1.0,
p_init_msg_list IN VARCHAR2 := fnd_api.g_true,
p_commit IN VARCHAR2 := FND_API.g_false,
p_validate_only IN VARCHAR2 := FND_API.g_true,
p_max_msg_count IN NUMBER := FND_API.g_miss_num,
p_ci_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
CURSOR items_c IS
SELECT object_relationship_id, object_id_to1, record_version_number
FROM pa_object_relationships
WHERE object_type_from = 'PA_CONTROL_ITEMS'
AND object_type_to = 'PA_CONTROL_ITEMS'
AND relationship_type = 'CI_INCLUDED_ITEM'
AND object_id_from1 = p_ci_id;
pa_debug.set_err_stack ('PA_CONTROL_ITEMS_PVT.DELETE_ALL_INCLUDED_CRS');
SAVEPOINT delete_all_included_crs;
pa_object_relationships_pkg.delete_row(
p_object_relationship_id => cur.object_relationship_id,
p_object_type_from => 'PA_CONTROL_ITEMS',
p_object_id_from1 => to_char(p_ci_id),
p_object_id_from2 => NULL,
p_object_id_from3 => NULL,
p_object_id_from4 => NULL,
p_object_id_from5 => NULL,
p_object_type_to => 'PA_CONTROL_ITEMS',
p_object_id_to1 => to_char(cur.object_id_to1),
p_object_id_to2 => NULL,
p_object_id_to3 => NULL,
p_object_id_to4 => NULL,
p_object_id_to5 => NULL,
p_pm_product_code => NULL,
p_record_version_number => cur.record_version_number,
x_return_status => x_return_status);
ROLLBACK TO delete_all_included_crs;
ROLLBACK TO delete_all_included_crs;
p_procedure_name => 'DELETE_ALL_INCLUDED_CRS',
p_error_text => SUBSTRB(SQLERRM,1,240));
END delete_all_included_crs;
SELECT object_id_to1 related_ci_id
FROM pa_object_relationships
WHERE object_type_from = 'PA_CONTROL_ITEMS'
AND object_type_to = 'PA_CONTROL_ITEMS'
AND relationship_type = 'CI_REFERENCED_ITEM'
AND object_id_from1 = p_from_ci_id
UNION ALL
SELECT object_id_from1 related_ci_id
FROM pa_object_relationships
WHERE object_type_from = 'PA_CONTROL_ITEMS'
AND object_type_to = 'PA_CONTROL_ITEMS'
AND relationship_type = 'CI_REFERENCED_ITEM'
AND object_id_to1 = p_from_ci_id;
select pps.project_system_status_code --status_code
from pa_control_items ci
,pa_project_statuses pps
where ci.ci_id = p_ci_id
and ci.status_code = pps.project_status_code;
SELECT
CI_ID
,CI_TYPE_ID
,SUMMARY
,STATUS_CODE
,OWNER_ID
,HIGHLIGHTED_FLAG
,PROGRESS_STATUS_CODE
,PROGRESS_AS_OF_DATE
,CLASSIFICATION_CODE_ID
,REASON_CODE_ID
,RECORD_VERSION_NUMBER
,PROJECT_ID
,LAST_MODIFICATION_DATE
,LAST_MODIFIED_BY_ID
,CREATION_DATE
,CREATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_LOGIN
,OBJECT_TYPE
,OBJECT_ID
,CI_NUMBER
,DATE_REQUIRED
,DATE_CLOSED
,CLOSED_BY_ID
,DESCRIPTION
,STATUS_OVERVIEW
,RESOLUTION
,RESOLUTION_CODE_ID
,PRIORITY_CODE
,EFFORT_LEVEL_CODE
,OPEN_ACTION_NUM
,PRICE
,PRICE_CURRENCY_CODE
,SOURCE_TYPE_CODE
,SOURCE_COMMENT
,SOURCE_NUMBER
,SOURCE_DATE_RECEIVED
,SOURCE_ORGANIZATION
,SOURCE_PERSON
,LAST_ACTION_NUMBER
,ATTRIBUTE_CATEGORY
,ATTRIBUTE1
,ATTRIBUTE2
,ATTRIBUTE3
,ATTRIBUTE4
,ATTRIBUTE5
,ATTRIBUTE6
,ATTRIBUTE7
,ATTRIBUTE8
,ATTRIBUTE9
,ATTRIBUTE10
,ATTRIBUTE11
,ATTRIBUTE12
,ATTRIBUTE13
,ATTRIBUTE14
,ATTRIBUTE15
,ORIG_SYSTEM_CODE
,ORIG_SYSTEM_REFERENCE
,VERSION_NUMBER
,CURRENT_VERSION_FLAG
,ORIGINAL_CI_ID
,SOURCE_CI_ID
,VERSION_COMMENTS
,CHANGE_APPROVER
,PCO_STATUS_CODE
,APPROVAL_TYPE_CODE
,LOCKED_FLAG
,PCO_SEQUENCE
FROM pa_control_items
WHERE ci_id = p_ci_id;
PA_CONTROL_ITEMS_PKG.UPDATE_ROW(
p_ci_id => p_ci_id
,p_ci_type_id => cp.ci_type_id
,p_summary => cp.summary
,p_status_code => cp.status_code
,p_owner_id => cp.owner_id
,p_highlighted_flag => cp.highlighted_flag
,p_progress_status_code => cp.progress_status_code
,p_progress_as_of_date => cp.progress_as_of_date
,p_classification_code => cp.classification_code_id
,p_reason_code => cp.reason_code_id
,p_record_version_number=> cp.record_version_number
,p_project_id => cp.project_id
,p_last_modified_by_id => NVL(PA_CONTROL_ITEMS_UTILS.GetPartyId(fnd_global.user_id ), fnd_global.user_id ) --cp.last_modified_by_id
,p_object_type => cp.object_type
,p_object_id => cp.object_id
,p_ci_number => cp.ci_number
,p_date_required => cp.date_required
,p_date_closed => cp.date_closed
,p_closed_by_id => cp.closed_by_id
,p_description => cp.description
,p_status_overview => cp.status_overview
,p_resolution => cp.resolution
,p_resolution_code => cp.resolution_code_id
,p_priority_code => cp.priority_code
,p_effort_level_code => cp.effort_level_code
,p_open_action_num => cp.open_action_num
,p_price => cp.price
,p_price_currency_code => cp.price_currency_code
,p_source_type_code => cp.source_type_code
,p_source_comment => cp.source_comment
,p_source_number => cp.source_number
,p_source_date_received => cp.source_date_received
,p_source_organization => cp.source_organization
,p_source_person => cp.source_person
,p_attribute_category => cp.attribute_category
,p_attribute1 => cp.attribute1
,p_attribute2 => cp.attribute2
,p_attribute3 => cp.attribute3
,p_attribute4 => cp.attribute4
,p_attribute5 => cp.attribute5
,p_attribute6 => cp.attribute6
,p_attribute7 => cp.attribute7
,p_attribute8 => cp.attribute8
,p_attribute9 => cp.attribute9
,p_attribute10 => cp.attribute10
,p_attribute11 => cp.attribute11
,p_attribute12 => cp.attribute12
,p_attribute13 => cp.attribute13
,p_attribute14 => cp.attribute14
,p_attribute15 => cp.attribute15
-- start: 26-Jun-2009 cklee Modified for the Bug# 8633676
,p_PCO_STATUS_CODE => cp.PCO_STATUS_CODE
,p_APPROVAL_TYPE_CODE => cp.APPROVAL_TYPE_CODE
,p_LOCKED_FLAG => 'Y' --cp.LOCKED_FLAG
-- end: 26-Jun-2009 cklee Modified for the Bug# 8633676
,p_Version_number => cp.Version_number
,p_Current_Version_flag => cp.Current_Version_flag
,p_Version_Comments => cp.Version_Comments
,p_Original_ci_id => cp.Original_ci_id
,p_Source_ci_id => cp.Source_ci_id
,p_change_approver => cp.change_approver
,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.user_id
);